Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ActiveX function in the DTS

Posted on 2009-07-10
9
Medium Priority
?
440 Views
Last Modified: 2012-05-07
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.

above

Open in new window

question.doc
0
Comment
Question by:salesprod
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 24824287
You don't need ActiveX for this you can use dynamic SQL and will be faster and easier to maintain. You can either put the code below in a stored procedure or directly in an Execute SQL task in the DTS package:

declare
	@sql varchar(8000)
select
	@sql='select * into Testing from [Testing_' + cast(month(getdate()-20) as varchar) + '_' + cast(year(getdate()-7) as varchar) +']'
	
print @sql	-- for testing only
EXEC (@sql)

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 24824333
If you still have to use the ActiveX code remove the single quotes around the table name and change the month and year to strings:

Probably would work without changing to strings but with them, will work for sure.
     strSQL = "select * into Testing from Testing_"& Cstr(month(now()-20)) &"_"&  Cstr(year(now()-7)) & ";"

Open in new window

0
 

Author Comment

by:salesprod
ID: 24824363
Thanks Zberteoc, Let me try it...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:salesprod
ID: 24824432
Awesome. It works. Thanks Zberteoc.
0
 

Author Comment

by:salesprod
ID: 24824610
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)
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 24827783
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)

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 24827811
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:


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)

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 24827816
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)

Open in new window

0
 

Author Comment

by:salesprod
ID: 24831826
got it. Thanks Zberteoc.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question