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 No. of queries thru another query

Dear Sir,

My database have a no. of tables. I have created separate sql files for different tables. Now i want to create all the tables by creating another sql file which will contain the individual sql files.

for example

USE DealSoft

EXEC("c:\SQLAccountTypes.sql")

EXEC("c:\\SQLAccounts.sql")

EXEC("c:\\SQLParties.sql")

EXEC command doesn't work this way. can u suggest the proper syntax.

with regards

wilfi

You could use xp_cmdshell for execute any external program and you could run osql script.sql from command line.

As result you could run:

Code Snippet

xp_cmdshell 'osql C:\yourScript.sql'

or (only in SQL Server 2005)

Code Snippet

xp_cmdshell 'sqlcmd C:\youScript.sql'

May be you need to configure account for executing external apps. You could use sp_xp_cmdshell_proxy_account stored procedure

|||

Wilfi,

If using SQL Server 2005, you might want to consider creating an SSIS package to perform such an action. With SQL Server 2000 that solution becomes "using a DTS package."

Another aspect of this matter would be to put your SQL scripts into stored procedures. You can have one stored procedure invoke multiple other stored procedures, similar to the BATCH action you have described. This plan works for any version of SQL Server, as far as I know.

Dan

|||

Dear Dan,

I would like to invoke my individual Stored Procedures thru a Master S.P. as suggested by you.

Can u tell me the syntax for the same with a small example(of Master S.P.).

thanking U.

With Regards,

wilfi

|||

Code Snippet

--Create procedures

CREATE PROCEDURE mySp1
as
BEGIN
PRINT 'Call To MySP1'
END
go

CREATE PROCEDURE mySp2
as
BEGIN
PRINT 'Call to MySP2'
END
go

CREATE PROCEDURE myMasterSP
as
BEGIN
EXEC mySP1
EXEC mySP2
END
go

-- Execute Master SP, you could do it any time after creating

myMasterSP

|||

Dear Sir,

Hearty Thanx for the immediate response. I could do as suggested by u.

with regards,

wilfi

|||

Konstantin,

Thanks! You beat me to it! ;-)

Dan

|||

Hey Konstantin, i was lookin for something related to inline store procs and saw your post..

do you know if those 2 last procs : mySP1 and mySP2 run async.

meaning, does the mySP2 proc waits for the mySP1 to be completed?.

It will be very helpful if you know!

anyways thanks in advance

Dave.

|||

Dave,

Sorry for butting in. All my experiences are that they run sequentially, in the order listed in the SP.

I would have all kinds of wrong answers in my computations were that not so.

Dan

|||

mySp1 and mySp2 run sync. Meaning the mySp2 wait for the mySp1 to be completed.

If you need async call, you could emulate this approach by using SQL Server Broker

Executing A No. of queries thru another query

Dear Sir,

My database have a no. of tables. I have created separate sql files for different tables. Now i want to create all the tables by creating another sql file which will contain the individual sql files.

for example

USE DealSoft

EXEC("c:\SQLAccountTypes.sql")

EXEC("c:\\SQLAccounts.sql")

EXEC("c:\\SQLParties.sql")

EXEC command doesn't work this way. can u suggest the proper syntax.

with regards

wilfi

You could use xp_cmdshell for execute any external program and you could run osql script.sql from command line.

As result you could run:

Code Snippet

xp_cmdshell 'osql C:\yourScript.sql'

or (only in SQL Server 2005)

Code Snippet

xp_cmdshell 'sqlcmd C:\youScript.sql'

May be you need to configure account for executing external apps. You could use sp_xp_cmdshell_proxy_account stored procedure

|||

Wilfi,

If using SQL Server 2005, you might want to consider creating an SSIS package to perform such an action. With SQL Server 2000 that solution becomes "using a DTS package."

Another aspect of this matter would be to put your SQL scripts into stored procedures. You can have one stored procedure invoke multiple other stored procedures, similar to the BATCH action you have described. This plan works for any version of SQL Server, as far as I know.

Dan

|||

Dear Dan,

I would like to invoke my individual Stored Procedures thru a Master S.P. as suggested by you.

Can u tell me the syntax for the same with a small example(of Master S.P.).

thanking U.

With Regards,

wilfi

|||

Code Snippet

--Create procedures

CREATE PROCEDURE mySp1
as
BEGIN
PRINT 'Call To MySP1'
END
go

CREATE PROCEDURE mySp2
as
BEGIN
PRINT 'Call to MySP2'
END
go

CREATE PROCEDURE myMasterSP
as
BEGIN
EXEC mySP1
EXEC mySP2
END
go

-- Execute Master SP, you could do it any time after creating

myMasterSP

|||

Dear Sir,

Hearty Thanx for the immediate response. I could do as suggested by u.

with regards,

wilfi

|||

Konstantin,

Thanks! You beat me to it! ;-)

Dan

|||

Hey Konstantin, i was lookin for something related to inline store procs and saw your post..

do you know if those 2 last procs : mySP1 and mySP2 run async.

meaning, does the mySP2 proc waits for the mySP1 to be completed?.

It will be very helpful if you know!

anyways thanks in advance

Dave.

|||

Dave,

Sorry for butting in. All my experiences are that they run sequentially, in the order listed in the SP.

I would have all kinds of wrong answers in my computations were that not so.

Dan

|||

mySp1 and mySp2 run sync. Meaning the mySp2 wait for the mySp1 to be completed.

If you need async call, you could emulate this approach by using SQL Server Broker

ExecuteScalar closed my connection?

Dear All,
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.sql

ExecuteScalar closed my connection?

Dear All,
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.
Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.
|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.

ExecuteScalar closed my connection?

Dear All,
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
--
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.

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 Statement..

Dear Friens,
I am writing a SP.But storeing a query in a variable.But at the time
of execution generating error.Exam
===================
Declare @.query varchar{500)
Set @.query = 'Select * from table'

if exists (exec (@.query))
print 'Hi'
====================
But "if exists" line giving error.How do I solve this.Please help me
out.
Reagrds
Arijit ChatterjeeHi

EXISTS requires a sub-query as the test, and EXEC does not do that.

If you have to do this dynamically then you may want to look at
sp_executesql to return a count or move everything into @.query.

If you posted more precise detail it may be easier to offer advice.

John

arijitchatterjee123@.yahoo.co.in (Arijit Chatterjee) wrote in message news:<ea01504d.0310302205.2ca98658@.posting.google.com>...
> Dear Friens,
> I am writing a SP.But storeing a query in a variable.But at the time
> of execution generating error.Exam
> ===================
> Declare @.query varchar{500)
> Set @.query = 'Select * from table'
> if exists (exec (@.query))
> print 'Hi'
> ====================
> But "if exists" line giving error.How do I solve this.Please help me
> out.
> Reagrds
> Arijit Chatterjee|||Create proc sp_test
as
Declare @.query varchar{500)
Declare @.var varchar(10)
set @.var='Test'
Set @.query = 'Select * from table' + ' Where ' + 'Colname = ' + @.var
exec (@.query)--> Working fine
if exists (exec (@.query))--> Sending error
print 'Hi'
Now tell me how to solve this.
Regards
Arijit Chatterjee|||See inline

"Arijit Chatterjee" <arijitchatterjee123@.yahoo.co.in> wrote in message
news:ea01504d.0311012028.19b506fb@.posting.google.c om...
> Create proc sp_test
> as
> Declare @.query varchar{500)
This will not compile

> Declare @.var varchar(10)
> set @.var='Test'
> Set @.query = 'Select * from table' + ' Where ' + 'Colname = ' + @.var
If you were doing this correctly the value in @.var would be enquoted

> exec (@.query)--> Working fine
> if exists (exec (@.query))--> Sending error
> print 'Hi'
> Now tell me how to solve this.

Reading books online would be the first place to look.

Then read http://www.algonet.se/~sommar/dynamic_sql.html on why you should
justify the use of dynamic SQL.

You should then be able to work out how to return the count using
sp_executesql to get what is require.

> Regards
> Arijit Chatterjee

John|||What you need to do in this case is create a sql server temporary
table (such as create table #temp (column1 nvarchar(10),column2
nvarchar(10))) and then build your @.query string so that it inserts
the results of your dynamic select statement into the temp table. You
can then play the whole "exists" game on the results of a query
against your #temp table.

Make sure that you do not build your temp table dynamically. And
remember, you can't insert results from a dynamically built select
statement into a normal variable, but you can insert them into a temp
table.

Good Luck!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<bo2oar$89l$1@.hercules.btinternet.com>...
> See inline
> "Arijit Chatterjee" <arijitchatterjee123@.yahoo.co.in> wrote in message
> news:ea01504d.0311012028.19b506fb@.posting.google.c om...
> > Create proc sp_test
> > as
> > Declare @.query varchar{500)
> This will not compile
> > Declare @.var varchar(10)
> > set @.var='Test'
> > Set @.query = 'Select * from table' + ' Where ' + 'Colname = ' + @.var
> If you were doing this correctly the value in @.var would be enquoted
> > exec (@.query)--> Working fine
> > if exists (exec (@.query))--> Sending error
> > print 'Hi'
> > Now tell me how to solve this.
> Reading books online would be the first place to look.
> Then read http://www.algonet.se/~sommar/dynamic_sql.html on why you should
> justify the use of dynamic SQL.
> You should then be able to work out how to return the count using
> sp_executesql to get what is require.
> > Regards
> > Arijit Chatterjee
> John

execute SSIS package in SQL Express environment

Dear all,

I have an SSIS package and want to execute it on a computer that only

has SQL Express installed on it. Since SQL Express didn't brought

dtexec or dtui so what should I do ?

Sorry for my bad English...

Thanks for advance,

SSIS is not contained in SQL Server express edition. You cannot run a package on box that only has the express edition. For more details on what is included on every version of the product see this:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||thanks for the reply..so there's nothing I can do ?|||

Sorry, I am a little bit confused. I am using SQL Server Developer Edition, which I believe is the same as SQL Server Express. Yesterday I was trying to find out how to do DTS in 2005 and found out on msdn that it had been renamed SQL Server Integration Services, and whilst I could not seem to start the SQL Server Import and Export Wizard via SQL Express as outlined in the knowledge base article, I could start it from the command line by running DTSWizard.exe manually as outlined in the article.

Or am I missing something?

|||Developers edition is not the same than Express edition; Developers edition includes SSIS.

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!!