basic question

hi i have the following variables.
declare @.viStock int
set @.viStock = 1000
declare @.vdStartDate datetime
declare @.vdEndDate datetime
set @.vdStartDate = getdate()
set @.vdEndDate = dateadd(w, 10, getdate())
i want to insert into a temp ttable the following
amount date
100 24-10-2005
100 31-11-2005
100 31-11-2005
.. and so on for 10 ws.
where amount = stock / @.vdStartDate - @.vdEndDate
how do i do this so that the amount is equally divided?
thankshere is the schema.
--
-- drop table temp
-- create table temp
-- (
-- amt int,
-- date datetime
-- )
--
declare @.viStock int
set @.viStock = 1000
declare @.vdStartDate datetime
declare @.vdEndDate datetime
set @.vdStartDate = getdate()
set @.vdEndDate = dateadd(w, 10, getdate())
insert into temp
(amt,date)
(
select @.viStock / datediff([w], @.vdStartDate, @.vdEndDate),
dateadd([w], 1, @.vdEndDate)
--group by @.viStock/ datediff(w, @.vdStartDate, @.vdEndDate), dateadd(w,
1, @.vdEndDate))
)
select * From temp
delete from temp
i cant use a group by here says -Server: Msg 164, Level 15, State 1, Line 20
GROUP BY expressions must refer to column names that appear in the select
list.
how do i correct that?
thanks
"ichor" <ichor@.hotmail.com> wrote in message
news:eJJ2wST2FHA.2564@.TK2MSFTNGP10.phx.gbl...
> hi i have the following variables.
> declare @.viStock int
> set @.viStock = 1000
> declare @.vdStartDate datetime
> declare @.vdEndDate datetime
> set @.vdStartDate = getdate()
> set @.vdEndDate = dateadd(w, 10, getdate())
>
> i want to insert into a temp ttable the following
> amount date
> 100 24-10-2005
> 100 31-11-2005
> 100 31-11-2005
> .. and so on for 10 ws.
> where amount = stock / @.vdStartDate - @.vdEndDate
> how do i do this so that the amount is equally divided?
>
> thanks
>|||On Tue, 25 Oct 2005 17:21:15 +1000, ichor wrote:

>hi i have the following variables.
>declare @.viStock int
>set @.viStock = 1000
>declare @.vdStartDate datetime
>declare @.vdEndDate datetime
>set @.vdStartDate = getdate()
>set @.vdEndDate = dateadd(w, 10, getdate())
>
>i want to insert into a temp ttable the following
>amount date
>100 24-10-2005
>100 31-11-2005
>100 31-11-2005
>.. and so on for 10 ws.
>where amount = stock / @.vdStartDate - @.vdEndDate
>how do i do this so that the amount is equally divided?
Hi ichor,
First step is to create and fill a numbers table. This is a one-time
job, and you'll find that there are many uses for a umbers table. Check
out http://www.aspfaq.com/show.asp?id=2516 for details on how to make a
numbers table and some examples of how to use it.
Now to the actual query:
SET @.NumWs = DATEDIFF(w, @.vdStartDate, @.vdEndDate)
INSERT INTO temptable (amount, [date])
SELECT @.viStock / @.NumWs,
DATEADD(w, Number-1, @.vdStartDate)
FROM Numbers
WHERE Number BETWEEN 1 AND @.NumWs
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Bad Directory- DTS - Global Variables

I am trying to set Global Variable that will open a txt file and import the
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
KarenHi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>

Bad Directory- DTS - Global Variables

I am trying to set Global Variable that will open a txt file and import the
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
KarenHi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>

Bad Directory- DTS - Global Variables

I am trying to set Global Variable that will open a txt file and import the
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
Karen
Hi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>
|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>