There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
If,Then,Else with Stored Procedure
I'm working on two DTS Packages. One exports 30000 records what meet criteria and the second exports all. The problem I have is that I need to control them with an if/else statement in this Stored Procedure and I don't know how.
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @OfficeID varchar(50)
DECLARE @path VARCHAR(8000)
DECLARE @id INT
DECLARE mdb_list CURSOR LOCAL SCROLL STATIC FOR
SELECT officeID, listpath
FROM tbl_office
WHERE startdate <= GETDATE() AND (enddate >= GETDATE() OR enddate IS NULL)
OPEN mdb_list
FETCH FIRST FROM mdb_list
INTO @OfficeID, @path
WHILE @@FETCH_STATUS = 0
BEGIN
IF @path IS NOT NULL AND @path <> ''
BEGIN
*****************
Here's where I need help....I need to check for records in tbl_template that match certain criteria. If there are at least X number of records, I need to run one DTS Package. If there are not at least X number of records, I need to run another.
*****************
END
FETCH NEXT FROM mdb_list
INTO @OfficeID, @path
END
CLOSE mdb_list
DEALLOCATE mdb_list
GO
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @OfficeID varchar(50)
DECLARE @path VARCHAR(8000)
DECLARE @id INT
DECLARE mdb_list CURSOR LOCAL SCROLL STATIC FOR
SELECT officeID, listpath
FROM tbl_office
WHERE startdate <= GETDATE() AND (enddate >= GETDATE() OR enddate IS NULL)
OPEN mdb_list
FETCH FIRST FROM mdb_list
INTO @OfficeID, @path
WHILE @@FETCH_STATUS = 0
BEGIN
IF @path IS NOT NULL AND @path <> ''
BEGIN
*****************
Here's where I need help....I need to check for records in tbl_template that match certain criteria. If there are at least X number of records, I need to run one DTS Package. If there are not at least X number of records, I need to run another.
*****************
END
FETCH NEXT FROM mdb_list
INTO @OfficeID, @path
END
CLOSE mdb_list
DEALLOCATE mdb_list
GO
Asked:
Who is Participating?
Declare a variable to hold your count
selelect @MyCount=Count(*) from blah, where blah
if @Mycount > somevalue Begin
dbo.xp_cmdshell('DTSRun /~Z0x050D69F27E2B010D8D319
else Begin
dbo.xp_cmdshell('DTSRun /~Z0x050D69F27E2B010D8D319
selelect @MyCount=Count(*) from blah, where blah
if @Mycount > somevalue Begin
dbo.xp_cmdshell('DTSRun /~Z0x050D69F27E2B010D8D319
else Begin
dbo.xp_cmdshell('DTSRun /~Z0x050D69F27E2B010D8D319
being as tbl_template could contain literally millions of qualified records is there a way to have it not count more than X#?
I just don't want to spend the resources sifting through millions of records when I only need to know if there's, say 2500.
you can alter your cursor to at least only select "valid" listpaths...
DECLARE mdb_list CURSOR LOCAL SCROLL STATIC FOR
SELECT officeID, listpath
FROM tbl_office
WHERE startdate <= GETDATE() AND
Getdate() <= coalesce(enddate,Getdate()
and coalesce(listpath,'') <> ''
OPEN mdb_list
FETCH FIRST FROM mdb_list
INTO @OfficeID, @path
WHILE @@FETCH_STATUS = 0
BEGIN
--IF @path IS NOT NULL AND @path <> ''
--BEGIN
there is no "real" way to limit the number of records you need sql server to count....
you could try
select count(*) from
(select top 2501 * from blah
where blah
order by blah ) as x
having count(*) > 2500
but that wont necessarily stop SQL server having to process every row to get a 2501 subset...
hth
DECLARE mdb_list CURSOR LOCAL SCROLL STATIC FOR
SELECT officeID, listpath
FROM tbl_office
WHERE startdate <= GETDATE() AND
Getdate() <= coalesce(enddate,Getdate()
and coalesce(listpath,'') <> ''
OPEN mdb_list
FETCH FIRST FROM mdb_list
INTO @OfficeID, @path
WHILE @@FETCH_STATUS = 0
BEGIN
--IF @path IS NOT NULL AND @path <> ''
--BEGIN
there is no "real" way to limit the number of records you need sql server to count....
you could try
select count(*) from
(select top 2501 * from blah
where blah
order by blah ) as x
having count(*) > 2500
but that wont necessarily stop SQL server having to process every row to get a 2501 subset...
hth
1 .you can use nested cursor:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp
--------------------------
2 .>is there a way to have it not count more than X#?
set rowcount x#
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp
--------------------------
2 .>is there a way to have it not count more than X#?
set rowcount x#
"but that wont necessarily stop SQL server having to process every row to get a 2501 subset..."
I think that's my problem anyway. I mean, what if it DOES have to look through 10,000,000 to find if there's 2500 that meet the criteria.
SO, it's just a performance issue I'll have to plan for.
I think that's my problem anyway. I mean, what if it DOES have to look through 10,000,000 to find if there's 2500 that meet the criteria.
SO, it's just a performance issue I'll have to plan for.
I think that'll do it, ignoring the performance issue, what I've got is this. Anybody see any problems or have any further comments?
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @OfficeID varchar(50)
DECLARE @path VARCHAR(8000)
DECLARE @id INT
DECLARE mdb_list CURSOR LOCAL SCROLL STATIC FOR
SELECT officeID, listpath
FROM tbl_office
WHERE startdate <= GETDATE() AND (enddate >= GETDATE() OR enddate IS NULL)
OPEN mdb_list
FETCH FIRST FROM mdb_list
INTO @OfficeID, @path
WHILE @@FETCH_STATUS = 0
BEGIN
IF @path IS NOT NULL AND @path <> ''
BEGIN
DECLARE @MyCount int
select @MyCount=Count(distinct homephone) from tbl_template where regionID = @OfficeID and exportID is null
if @MyCount > 2500
BEGIN
SELECT @script = 'dtsrun /S "' + @@SERVERNAME + '" /E /N "exportLists" ' +
'/A MDBFile="' + @path + '" '
END
ELSE
BEGIN
SELECT @script = 'dtsrun /S "' + @@SERVERNAME + '" /E /N "resetTemplate" ' +
'/A OfficeID="' + @OfficeID +'"'
END
END
FETCH NEXT FROM mdb_list
INTO @OfficeID, @path
END
CLOSE mdb_list
DEALLOCATE mdb_list
GO
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @OfficeID varchar(50)
DECLARE @path VARCHAR(8000)
DECLARE @id INT
DECLARE mdb_list CURSOR LOCAL SCROLL STATIC FOR
SELECT officeID, listpath
FROM tbl_office
WHERE startdate <= GETDATE() AND (enddate >= GETDATE() OR enddate IS NULL)
OPEN mdb_list
FETCH FIRST FROM mdb_list
INTO @OfficeID, @path
WHILE @@FETCH_STATUS = 0
BEGIN
IF @path IS NOT NULL AND @path <> ''
BEGIN
DECLARE @MyCount int
select @MyCount=Count(distinct homephone) from tbl_template where regionID = @OfficeID and exportID is null
if @MyCount > 2500
BEGIN
SELECT @script = 'dtsrun /S "' + @@SERVERNAME + '" /E /N "exportLists" ' +
'/A MDBFile="' + @path + '" '
END
ELSE
BEGIN
SELECT @script = 'dtsrun /S "' + @@SERVERNAME + '" /E /N "resetTemplate" ' +
'/A OfficeID="' + @OfficeID +'"'
END
END
FETCH NEXT FROM mdb_list
INTO @OfficeID, @path
END
CLOSE mdb_list
DEALLOCATE mdb_list
GO
OK you don't need the cursors
and i believe that this will be much more efficient
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @id INT
select regionid,count(distinct homephone) as phone
into #phone
from tbl_template
Where exportid is null
group by regionid
select officeid,listpath
into #office
from tbl_office
WHERE startdate <= GETDATE()
AND (enddate >= GETDATE() OR enddate IS NULL)
and listpath is not null
and listpath <> ''
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
Select @script='Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd
, @id=@id+1
from #temp
Where rowno = @id
-- invoke @script as you desire
End
Return
and i believe that this will be much more efficient
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @id INT
select regionid,count(distinct homephone) as phone
into #phone
from tbl_template
Where exportid is null
group by regionid
select officeid,listpath
into #office
from tbl_office
WHERE startdate <= GETDATE()
AND (enddate >= GETDATE() OR enddate IS NULL)
and listpath is not null
and listpath <> ''
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
Select @script='Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd
, @id=@id+1
from #temp
Where rowno = @id
-- invoke @script as you desire
End
Return
Thanks lowfatspread. I can see what you're doing there but I'm an idiot so I probably wont understand for at least an hour.
so, first, if you wouldn't mind elaborating on the reasons that's more efficient, I'm sure the information wouldn't hurt me.
And forgive me for being stupid but what does "into #phone" do? Is it some kind of variable??? Sorry, I'm obviously new to this...
Second, I actually changed a part on Friday.
--old
if @MyCount > 2500
BEGIN
SELECT @script = 'dtsrun /S "' + @@SERVERNAME + '" /E /N "exportLists" ' +
'/A MDBFile="' + @path + '" '
END
ELSE
--new
if @MyCount > 2500
BEGIN
SELECT @script = 'dtsrun /S "' + @@SERVERNAME + '" /E /N "exportLists" ' +
'/A MDBFile="' + @path + '" ' +
'/A OfficeID="' + @OfficeID + '" ' +
'/A ExportID="' + @ExportID + '" '
END
ELSE
END
So I'm assuming that I'd do something like...
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + @OfficeID + '" ' + '/A ExportID="' + @ExportID + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
And finally I also have to add another query to actually get the correct exportID.
Select top 1 listid from tbl_sf3Lists order by listID desc
What's the best way to get the results+1 into @ExportID???
so, first, if you wouldn't mind elaborating on the reasons that's more efficient, I'm sure the information wouldn't hurt me.
And forgive me for being stupid but what does "into #phone" do? Is it some kind of variable??? Sorry, I'm obviously new to this...
Second, I actually changed a part on Friday.
--old
if @MyCount > 2500
BEGIN
SELECT @script = 'dtsrun /S "' + @@SERVERNAME + '" /E /N "exportLists" ' +
'/A MDBFile="' + @path + '" '
END
ELSE
--new
if @MyCount > 2500
BEGIN
SELECT @script = 'dtsrun /S "' + @@SERVERNAME + '" /E /N "exportLists" ' +
'/A MDBFile="' + @path + '" ' +
'/A OfficeID="' + @OfficeID + '" ' +
'/A ExportID="' + @ExportID + '" '
END
ELSE
END
So I'm assuming that I'd do something like...
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + @OfficeID + '" ' + '/A ExportID="' + @ExportID + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
And finally I also have to add another query to actually get the correct exportID.
Select top 1 listid from tbl_sf3Lists order by listID desc
What's the best way to get the results+1 into @ExportID???
>>so, first, if you wouldn't mind elaborating on the reasons that's more efficient, I'm sure the information wouldn't hurt me.
>>And forgive me for being stupid but what does "into #phone" do? Is it some kind of variable??? Sorry, I'm obviously new to this...
well MS SQL Server Cursors are very slow compared to allowing it to perform set based operations is the basic reason...
select ... into #phone from ....
this is a ms sql server SQL Extension which allows you to create a table through the select statement without
having to pre define it with a create table statement and then insert into it...
since the table name begins with a # it is a TEMPORARY TABLE
finally the code hopefully allows for the minimum number of passes of the source tables , in the most efficient manner
available to the Database Engine...
change this
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
Select @script='Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd
, @id=@id+1
from #temp
Where rowno = @id
-- invoke @script as you desire
End
to
Declare @exportid int
Select @exportid=coalesce(Max(lis
/*
-- you don't make it clear how tbl_sf3lists is maintained ...
Update tbl_sf3lists
set listid=listid + coalesce((select count(*) from #temp where phones>2500),0)
*/
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + OfficeID + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
,phones
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
Select @script='Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd + case when phones>2500 then '/A ExportID="' + convert(varchar(10),@expor
,@exportid = @exportid + case when phones > 2500 then 1 else 0 end
, @id=@id+1
from #temp
Where rowno = @id
-- invoke @script as you desire
End
hth
>>And forgive me for being stupid but what does "into #phone" do? Is it some kind of variable??? Sorry, I'm obviously new to this...
well MS SQL Server Cursors are very slow compared to allowing it to perform set based operations is the basic reason...
select ... into #phone from ....
this is a ms sql server SQL Extension which allows you to create a table through the select statement without
having to pre define it with a create table statement and then insert into it...
since the table name begins with a # it is a TEMPORARY TABLE
finally the code hopefully allows for the minimum number of passes of the source tables , in the most efficient manner
available to the Database Engine...
change this
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
Select @script='Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd
, @id=@id+1
from #temp
Where rowno = @id
-- invoke @script as you desire
End
to
Declare @exportid int
Select @exportid=coalesce(Max(lis
/*
-- you don't make it clear how tbl_sf3lists is maintained ...
Update tbl_sf3lists
set listid=listid + coalesce((select count(*) from #temp where phones>2500),0)
*/
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + OfficeID + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
,phones
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
Select @script='Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd + case when phones>2500 then '/A ExportID="' + convert(varchar(10),@expor
,@exportid = @exportid + case when phones > 2500 then 1 else 0 end
, @id=@id+1
from #temp
Where rowno = @id
-- invoke @script as you desire
End
hth
"well MS SQL Server Cursors are very slow compared to allowing it to perform set based operations is the basic reason..."
So essentially we're creating temp tables and querying them. Is there any kind of cleanup required for the temp tables or do they get disposed of naturally????
OK, the update to tbl_sf3Lists actually happens in the exportLists package.
I'm only using the select statement in this context to get the greatest listID and that value gets passed to through the call for that package.
So at this point, I think the following would work
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @id INT
select regionid,count(distinct homephone) as phone
into #phone
from tbl_template
Where exportid is null
group by regionid
select officeid,listpath
into #office
from tbl_office
WHERE startdate <= GETDATE()
AND (enddate >= GETDATE() OR enddate IS NULL)
and listpath is not null
and listpath <> ''
Select top 1 listid
into @exportID
from tbl_sf3Lists
order by listID desc
Declare @exportid int
Select @exportid=coalesce(Max(lis
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + OfficeID + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
,phones
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
Select @script='Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd + case when phones>2500 then '/A ExportID="' + convert(varchar(10),@expor
,@exportid = @exportid + case when phones > 2500 then 1 else 0 end
, @id=@id+1
from #temp
Where rowno = @id
-- invoke @script as you desire
End
Return
So essentially we're creating temp tables and querying them. Is there any kind of cleanup required for the temp tables or do they get disposed of naturally????
OK, the update to tbl_sf3Lists actually happens in the exportLists package.
I'm only using the select statement in this context to get the greatest listID and that value gets passed to through the call for that package.
So at this point, I think the following would work
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @id INT
select regionid,count(distinct homephone) as phone
into #phone
from tbl_template
Where exportid is null
group by regionid
select officeid,listpath
into #office
from tbl_office
WHERE startdate <= GETDATE()
AND (enddate >= GETDATE() OR enddate IS NULL)
and listpath is not null
and listpath <> ''
Select top 1 listid
into @exportID
from tbl_sf3Lists
order by listID desc
Declare @exportid int
Select @exportid=coalesce(Max(lis
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + OfficeID + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
,phones
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
Select @script='Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd + case when phones>2500 then '/A ExportID="' + convert(varchar(10),@expor
,@exportid = @exportid + case when phones > 2500 then 1 else 0 end
, @id=@id+1
from #temp
Where rowno = @id
-- invoke @script as you desire
End
Return
I'm pretty sure that takes care of it.
I haven't been able to test it yet because of a problem with one of my activeX scripts in the DTS package but the logic is there.
It would sure help if you've got any idea why...
Function Main()
Dim task
Set task = DTSGlobalVariables.Parent.
task.SQLStatement = "insert into tbl_sf3lists (listname, totalRecords, availableRecords, officeID, createDate, notes) VALUES ('" + DTSGlobalVariables("export
Main = DTSTaskExecResult_Success
End Function
Gets an error: Type Mismatch: '[string: "insert into tbl_sf3l"]'
I'm sure it's my syntax somewhere...
I haven't been able to test it yet because of a problem with one of my activeX scripts in the DTS package but the logic is there.
It would sure help if you've got any idea why...
Function Main()
Dim task
Set task = DTSGlobalVariables.Parent.
task.SQLStatement = "insert into tbl_sf3lists (listname, totalRecords, availableRecords, officeID, createDate, notes) VALUES ('" + DTSGlobalVariables("export
Main = DTSTaskExecResult_Success
End Function
Gets an error: Type Mismatch: '[string: "insert into tbl_sf3l"]'
I'm sure it's my syntax somewhere...
Hey, hope you're still around, finally got around to testing this and I'm getting an error I hope you can help me with...
Error 170: Line 40: Incorrect Syntax near '='.
then '/A ExportID="' + convert(varchar(10),(Selec
This is what I've got...
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @id INT
DECLARE @max INT
DECLARE @exportID INT
select regionid,count(distinct homephone) as phone
into #phone
from tbl_template
Where exportid is null
group by regionid
select officeid,listpath
into #office
from tbl_office
WHERE startdate <= GETDATE()
AND (enddate >= GETDATE() OR enddate IS NULL)
and listpath is not null
and listpath <> ''
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + OfficeID + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
,phones
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
Select @script='"Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd
+ case when phones>2500
then '/A ExportID="' + convert(varchar(10),(Selec
else ' ' end
, @id=@id+1
from #temp
Where rowno = @id
End
Return
Error 170: Line 40: Incorrect Syntax near '='.
then '/A ExportID="' + convert(varchar(10),(Selec
This is what I've got...
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @id INT
DECLARE @max INT
DECLARE @exportID INT
select regionid,count(distinct homephone) as phone
into #phone
from tbl_template
Where exportid is null
group by regionid
select officeid,listpath
into #office
from tbl_office
WHERE startdate <= GETDATE()
AND (enddate >= GETDATE() OR enddate IS NULL)
and listpath is not null
and listpath <> ''
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + OfficeID + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
,phones
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
Select @script='"Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd
+ case when phones>2500
then '/A ExportID="' + convert(varchar(10),(Selec
else ' ' end
, @id=@id+1
from #temp
Where rowno = @id
End
Return
sorry try
then '/A ExportID="' + convert(varchar(10),(Selec
and you can probably remove any reference to @exportid ...
hth
then '/A ExportID="' + convert(varchar(10),(Selec
and you can probably remove any reference to @exportid ...
hth
Had to change the phones(es) to phone(s).
ie. phones>2500 = phone > 2500
If that's correct then I've got one more error it seems
Server: Msg 245, Level 16, State 1, Procedure exportLists, Line 22
Syntax error converting the varchar value '"resetTemplate" /A Officeid="' to a column of data type int.
ie. phones>2500 = phone > 2500
If that's correct then I've got one more error it seems
Server: Msg 245, Level 16, State 1, Procedure exportLists, Line 22
Syntax error converting the varchar value '"resetTemplate" /A Officeid="' to a column of data type int.
Thanks for replying. I haven't been able to figure this one out.
well, the error stopped but it still doesn't seem to be executing the DTS Packages.
I just get
(3 row(s) affected)
(3 row(s) affected)
(3 row(s) affected)
but nothing else is done.
It's like it's looping through the stored procedure but not executing any of the DTS packages for eah loop.
well, the error stopped but it still doesn't seem to be executing the DTS Packages.
I just get
(3 row(s) affected)
(3 row(s) affected)
(3 row(s) affected)
but nothing else is done.
It's like it's looping through the stored procedure but not executing any of the DTS packages for eah loop.
OK, There was a problem with the DTS Package but I fixed it.
I had to add an ActiveX task to set the Access connection to the variable.
Now it works when I populate the variables.
However, the Stored Procedure you helped me create still gets the same results...it doesn't execute the package.
Any help would really be appreciated.
I had to add an ActiveX task to set the Access connection to the variable.
Now it works when I populate the variables.
However, the Stored Procedure you helped me create still gets the same results...it doesn't execute the package.
Any help would really be appreciated.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month10 days, 20 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
Yes...
& Yes The will be dropped automatically on exit from the procedure....
>>OK, the update to tbl_sf3Lists actually happens in the exportLists package.
>>I'm only using the select statement in this context to get the greatest listID and that value gets passed to through the call for that package.
so each invocation will have to retrieve the latest value... it needs to go in the while loop
>>So at this point, I think the following would work
no more like this
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @id INT
select regionid,count(distinct homephone) as phone
into #phone
from tbl_template
Where exportid is null
group by regionid
select officeid,listpath
into #office
from tbl_office
WHERE startdate <= GETDATE()
AND (enddate >= GETDATE() OR enddate IS NULL)
and listpath is not null
and listpath <> ''
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + OfficeID + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
,phones
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
-- it looks like you can use Max to determine the listid........ which is preferable to the TOP 1 ... Desc code
-- but if i'm wrong then replace it with your code...
Select @script='Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd
+ case when phones>2500
then '/A ExportID="' + convert(varchar(10),(Selec
else ' ' end
, @id=@id+1
from #temp
Where rowno = @id
-- invoke @script as you desire
End
Return
hth