salesprod
asked on
ActiveX function in the DTS
Hello Expert,
I am trying to create a DTS to generate a new table based on a monthly historical table. Since the table name changes every month, therefore I am trying to create a ActiveX function to pick up the latest monthly table. For example, for June, the table name will be testing_6_2009, and for May, it will be testing_5_2009 and so on.
Here is the function I am trying to use:
'************************* ********** ********** ********** ********** *****
' Visual Basic ActiveX Script
'************************* ********** ********** ********** ********** *******
Function Main()
Dim strSQL
strSQL = "select * into Testing from 'Testing_"& month(now()-20) &"_"& year(now()-7) & "';"
DTSGlobalVariables(TestingSQL").Value = strSQL
Main = DTSTaskExecResult_Success
End Function
This is the steps I did so far:
Step1: I used a Execute SQL task to generate a new table called Testing
Step2: I used a ActiveX Script Task to hold the above function
Step3: I used a Dynamic Properties Task to pass the variable
Step4: I used a Execute SQL Task to hold the following SQL to generate the new "Testing" table
select * into testing from 'Testing_?_?';
The first 3 steps run successfully, when I execute the fourth step, it gave me the following error message:
Incorrect syntax near Testing_6_2009
Please suggest what I did wrong. Thanks for your help. Have a great day.
I am trying to create a DTS to generate a new table based on a monthly historical table. Since the table name changes every month, therefore I am trying to create a ActiveX function to pick up the latest monthly table. For example, for June, the table name will be testing_6_2009, and for May, it will be testing_5_2009 and so on.
Here is the function I am trying to use:
'*************************
' Visual Basic ActiveX Script
'*************************
Function Main()
Dim strSQL
strSQL = "select * into Testing from 'Testing_"& month(now()-20) &"_"& year(now()-7) & "';"
DTSGlobalVariables(TestingSQL").Value = strSQL
Main = DTSTaskExecResult_Success
End Function
This is the steps I did so far:
Step1: I used a Execute SQL task to generate a new table called Testing
Step2: I used a ActiveX Script Task to hold the above function
Step3: I used a Dynamic Properties Task to pass the variable
Step4: I used a Execute SQL Task to hold the following SQL to generate the new "Testing" table
select * into testing from 'Testing_?_?';
The first 3 steps run successfully, when I execute the fourth step, it gave me the following error message:
Incorrect syntax near Testing_6_2009
Please suggest what I did wrong. Thanks for your help. Have a great day.
above
question.doc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Zberteoc, Let me try it...
ASKER
Awesome. It works. Thanks Zberteoc.
ASKER
Hi Zberteoc,
If i want to create the new testing table to a different database called "Sales" and I try to use the script you gave to me as follows. Why it does not work?
declare
@sql varchar(8000)
select
@sql='select * into Testing from [Sales.dbo.Testing_' + cast(month(getdate()-20) as varchar) + '_' + cast(year(getdate()-7) as varchar) +']'
print @sql -- for testing only
EXEC (@sql)
If i want to create the new testing table to a different database called "Sales" and I try to use the script you gave to me as follows. Why it does not work?
declare
@sql varchar(8000)
select
@sql='select * into Testing from [Sales.dbo.Testing_' + cast(month(getdate()-20) as varchar) + '_' + cast(year(getdate()-7) as varchar) +']'
print @sql -- for testing only
EXEC (@sql)
Because the Sales.dbo.Testing_...(date here) doesn't exist yet. You'll have to create it first or if you want to use the same table from the initial db then you'll have to qualify the table with the database:
declare
@sql varchar(8000)
select
@sql='select * into Testing from [DATABASE_NAME]..[Testing_' + cast(month(getdate()-20) as varchar) + '_' + cast(year(getdate()-7) as varchar) +']'
print @sql -- for testing only
EXEC (@sql)
Oh, sorry, now I see the problem, is the way you use the brakets, []. The brackets has to wrap individually the database name then the owner(schema) and then the object, table in this case. The [] are only necessary if the names are the same with key words like table, month etc or if they have spaces inside:
select * from [Month] or select * from [Table] (if the table's name is really Table)
or
select * from [Sales Table]
otherwise they are not necessary.
So in order the cod to work you need:
select * from [Month] or select * from [Table] (if the table's name is really Table)
or
select * from [Sales Table]
otherwise they are not necessary.
So in order the cod to work you need:
declare
@sql varchar(8000)
select
@sql='select * into Testing from [Sales].[dbo[.[Testing_' + cast(month(getdate()-20) as varchar) + '_' + cast(year(getdate()-7) as varchar) +']'
print @sql -- for testing only
EXEC (@sql)
sorry:
declare
@sql varchar(8000)
select
@sql='select * into Testing from [Sales].[dbo].[Testing_' + cast(month(getdate()-20) as varchar) + '_' + cast(year(getdate()-7) as varchar) +']'
print @sql -- for testing only
EXEC (@sql)
ASKER
got it. Thanks Zberteoc.
Probably would work without changing to strings but with them, will work for sure.
Open in new window