Quote:
Originally Posted by megastar5
I Had A Text File In Which There Are Sqlstatements.i Want To Execute The Entire File In Query Analyzer.what Command I Must Use For That?
Open the file in Query Analyzer, then hit F5.
Showing posts with label analyzer. Show all posts
Showing posts with label analyzer. Show all posts
Friday, March 23, 2012
Executing A File In Sql Query Analyzer
I Had A Text File In Which There Are Sqlstatements.i Want To Execute The Entire File In Query Analyzer.what Command I Must Use For That?
Open the file in Query Analyzer, then hit F5.
Open the file in Query Analyzer, then hit F5.
Monday, March 12, 2012
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
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
Friday, February 24, 2012
EXECUTE permission denied on object 'sp_sdidebug', database 'maste
What permissions need to be granted to developers to allow them to debug a
procedure in query analyzer.
Thanks
RonRon,
Try adding the user to the Master system database and then GRANT permissions
to execute the sp_sdidebug system stored procedure.
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp
HTH
Jerry
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DCC04B2B-AA46-41F2-813F-1E5DC1C16DF8@.microsoft.com...
> What permissions need to be granted to developers to allow them to debug a
> procedure in query analyzer.
> Thanks
> Ron
procedure in query analyzer.
Thanks
RonRon,
Try adding the user to the Master system database and then GRANT permissions
to execute the sp_sdidebug system stored procedure.
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp
HTH
Jerry
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DCC04B2B-AA46-41F2-813F-1E5DC1C16DF8@.microsoft.com...
> What permissions need to be granted to developers to allow them to debug a
> procedure in query analyzer.
> Thanks
> Ron
Sunday, February 19, 2012
Execute package from query analyzer
I have SQL 2K and I have created a local package to import data from Access
mdb and I have saved it.
I would like to know if this can be executed from SQL query analyzer.
Thanks
DimitrisHi
Use xp_cmdshell with DTSRun program that run the Package
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||If you create a job for your package you can :-
USE msdb
EXEC sp_start_job @.job_name = 'Nightly Backup'
If not you can utilise DTSRUN :-
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
/Mpackage_password
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||I have tried:
USE msdb
EXEC sp_start_job @.job_name = 'FACOM IMPORT'
and I get an error:
The specified @.job_name ('FACOM IMPORT') does not exist.
The package I have created is saved in Data Transformation Services -->
Local Packages
Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
> If you create a job for your package you can :-
> USE msdb
> EXEC sp_start_job @.job_name = 'Nightly Backup'
>
> If not you can utilise DTSRUN :-
> To execute a DTS package saved in the SQL Server msdb database, use:
> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
> /Mpackage_password
>
> --
> HTH. Ryan
>
> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
> news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>>I have SQL 2K and I have created a local package to import data from
>>Access mdb and I have saved it.
>> I would like to know if this can be executed from SQL query analyzer.
>> Thanks
>> Dimitris
>|||sp_start_job starts a SQL Server agent job, not a package. You can create a job start in turn starts
the package, or perhaps use xp_cmdshell as already suggested.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:%23$uWg0MeGHA.4304@.TK2MSFTNGP05.phx.gbl...
>I have tried:
> USE msdb
> EXEC sp_start_job @.job_name = 'FACOM IMPORT'
> and I get an error:
> The specified @.job_name ('FACOM IMPORT') does not exist.
> The package I have created is saved in Data Transformation Services --> Local Packages
> Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
> news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>> If you create a job for your package you can :-
>> USE msdb
>> EXEC sp_start_job @.job_name = 'Nightly Backup'
>>
>> If not you can utilise DTSRUN :-
>> To execute a DTS package saved in the SQL Server msdb database, use:
>> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password
>>
>> --
>> HTH. Ryan
>>
>> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>>I have SQL 2K and I have created a local package to import data from Access mdb and I have saved
>>it.
>> I would like to know if this can be executed from SQL query analyzer.
>> Thanks
>> Dimitris
>>
>
mdb and I have saved it.
I would like to know if this can be executed from SQL query analyzer.
Thanks
DimitrisHi
Use xp_cmdshell with DTSRun program that run the Package
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||If you create a job for your package you can :-
USE msdb
EXEC sp_start_job @.job_name = 'Nightly Backup'
If not you can utilise DTSRUN :-
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
/Mpackage_password
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||I have tried:
USE msdb
EXEC sp_start_job @.job_name = 'FACOM IMPORT'
and I get an error:
The specified @.job_name ('FACOM IMPORT') does not exist.
The package I have created is saved in Data Transformation Services -->
Local Packages
Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
> If you create a job for your package you can :-
> USE msdb
> EXEC sp_start_job @.job_name = 'Nightly Backup'
>
> If not you can utilise DTSRUN :-
> To execute a DTS package saved in the SQL Server msdb database, use:
> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
> /Mpackage_password
>
> --
> HTH. Ryan
>
> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
> news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>>I have SQL 2K and I have created a local package to import data from
>>Access mdb and I have saved it.
>> I would like to know if this can be executed from SQL query analyzer.
>> Thanks
>> Dimitris
>|||sp_start_job starts a SQL Server agent job, not a package. You can create a job start in turn starts
the package, or perhaps use xp_cmdshell as already suggested.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:%23$uWg0MeGHA.4304@.TK2MSFTNGP05.phx.gbl...
>I have tried:
> USE msdb
> EXEC sp_start_job @.job_name = 'FACOM IMPORT'
> and I get an error:
> The specified @.job_name ('FACOM IMPORT') does not exist.
> The package I have created is saved in Data Transformation Services --> Local Packages
> Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
> news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>> If you create a job for your package you can :-
>> USE msdb
>> EXEC sp_start_job @.job_name = 'Nightly Backup'
>>
>> If not you can utilise DTSRUN :-
>> To execute a DTS package saved in the SQL Server msdb database, use:
>> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password
>>
>> --
>> HTH. Ryan
>>
>> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>>I have SQL 2K and I have created a local package to import data from Access mdb and I have saved
>>it.
>> I would like to know if this can be executed from SQL query analyzer.
>> Thanks
>> Dimitris
>>
>
Execute package from query analyzer
I have SQL 2K and I have created a local package to import data from Access
mdb and I have saved it.
I would like to know if this can be executed from SQL query analyzer.
Thanks
DimitrisHi
Use xp_cmdshell with DTSRun program that run the Package
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||If you create a job for your package you can :-
USE msdb
EXEC sp_start_job @.job_name = 'Nightly Backup'
If not you can utilise DTSRUN :-
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
/Mpackage_password
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||I have tried:
USE msdb
EXEC sp_start_job @.job_name = 'FACOM IMPORT'
and I get an error:
The specified @.job_name ('FACOM IMPORT') does not exist.
The package I have created is saved in Data Transformation Services -->
Local Packages
"Ryan" <Ryan_Waight@.nospam.hotmail.com>
news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
> If you create a job for your package you can :-
> USE msdb
> EXEC sp_start_job @.job_name = 'Nightly Backup'
>
> If not you can utilise DTSRUN :-
> To execute a DTS package saved in the SQL Server msdb database, use:
> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
> /Mpackage_password
>
> --
> HTH. Ryan
>
> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
> news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>|||sp_start_job starts a SQL Server agent job, not a package. You can create a
job start in turn starts
the package, or perhaps use xp_cmdshell as already suggested.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:%23$uWg0MeGHA.4304@.TK2MSFTNGP05.phx
.gbl...
>I have tried:
> USE msdb
> EXEC sp_start_job @.job_name = 'FACOM IMPORT'
> and I get an error:
> The specified @.job_name ('FACOM IMPORT') does not exist.
> The package I have created is saved in Data Transformation Services --> Lo
cal Packages
> "Ryan" <Ryan_Waight@.nospam.hotmail.com>
> news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>
mdb and I have saved it.
I would like to know if this can be executed from SQL query analyzer.
Thanks
DimitrisHi
Use xp_cmdshell with DTSRun program that run the Package
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||If you create a job for your package you can :-
USE msdb
EXEC sp_start_job @.job_name = 'Nightly Backup'
If not you can utilise DTSRUN :-
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
/Mpackage_password
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||I have tried:
USE msdb
EXEC sp_start_job @.job_name = 'FACOM IMPORT'
and I get an error:
The specified @.job_name ('FACOM IMPORT') does not exist.
The package I have created is saved in Data Transformation Services -->
Local Packages
"Ryan" <Ryan_Waight@.nospam.hotmail.com>
news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
> If you create a job for your package you can :-
> USE msdb
> EXEC sp_start_job @.job_name = 'Nightly Backup'
>
> If not you can utilise DTSRUN :-
> To execute a DTS package saved in the SQL Server msdb database, use:
> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
> /Mpackage_password
>
> --
> HTH. Ryan
>
> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
> news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>|||sp_start_job starts a SQL Server agent job, not a package. You can create a
job start in turn starts
the package, or perhaps use xp_cmdshell as already suggested.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:%23$uWg0MeGHA.4304@.TK2MSFTNGP05.phx
.gbl...
>I have tried:
> USE msdb
> EXEC sp_start_job @.job_name = 'FACOM IMPORT'
> and I get an error:
> The specified @.job_name ('FACOM IMPORT') does not exist.
> The package I have created is saved in Data Transformation Services --> Lo
cal Packages
> "Ryan" <Ryan_Waight@.nospam.hotmail.com>
> news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>
Wednesday, February 15, 2012
Execute DTS in Query Analyzer
I am trying to execute a DTS package from the query analyzer and eventually putting that code into a stored procedure. The DTS package is a simple import from a text file into a table. When I execute the package from the Enteprise Manager it works fine. When I try to execute it from the Query Analyzer it says the 'The command(s) completed successfully.' however no data was inserted. Is there anything I need to do to the package to enable it to be executed externally? Please help!
Here is the code that I am using:
DECLARE @.oPKG int
DECLARE @.hr int
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
RETURN
END
EXEC @.hr = sp_OAMethod @.oPKG, 'LoadFromSqlServer', NULL,
@.ServerName='MyServer', @.PackageName='MyDTSPackage', @.Flags=256
IF @.hr <> 0
BEGIN
PRINT '*** Load Package failed'
RETURN
END
--Executing the Package:
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
RETURN
END
--Cleaning up:
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
RETURN
END
If anyone can help, I'd really appreciate it. Thanks in advance.Hey Dkwon
I've had exactly the same problem as you. I never got the package to work through Query Analyzer, but I think I know what causes it.
When you run the package through Enterpirse manager you are using your own login and therefore have access to objects that your login has permission to access.
As soon as you use sp_OACreate it creates an instance of the OLE object (in this case a DTS package) on the server. Thus, the package is being run from the server, and the server may not have permission to execute the package. Try run the package through enterprise manager on the server and see what happens?
Hope this helps. Let us know how you fare.
Lionel|||I'm still looking for a solution to this problem if anyone has one. How do I get the server to have the right permission? --John
Here is the code that I am using:
DECLARE @.oPKG int
DECLARE @.hr int
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
RETURN
END
EXEC @.hr = sp_OAMethod @.oPKG, 'LoadFromSqlServer', NULL,
@.ServerName='MyServer', @.PackageName='MyDTSPackage', @.Flags=256
IF @.hr <> 0
BEGIN
PRINT '*** Load Package failed'
RETURN
END
--Executing the Package:
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
RETURN
END
--Cleaning up:
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
RETURN
END
If anyone can help, I'd really appreciate it. Thanks in advance.Hey Dkwon
I've had exactly the same problem as you. I never got the package to work through Query Analyzer, but I think I know what causes it.
When you run the package through Enterpirse manager you are using your own login and therefore have access to objects that your login has permission to access.
As soon as you use sp_OACreate it creates an instance of the OLE object (in this case a DTS package) on the server. Thus, the package is being run from the server, and the server may not have permission to execute the package. Try run the package through enterprise manager on the server and see what happens?
Hope this helps. Let us know how you fare.
Lionel|||I'm still looking for a solution to this problem if anyone has one. How do I get the server to have the right permission? --John
Subscribe to:
Posts (Atom)