February 5, 2008 at 5:57 am
Hi,
I am trying to create a series of MS access tables from within a SQL 2000 DTS package. The names of the tables reflect the date range of the data within them. Creating the "Create table ddmmyy ...." statement isn't a problem (full code below), but doesn't run as it creates a Jet error - "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. Does anyone know if what I'm trying to do is possible.
Before anyone suggests it, yes I have posted this on Access forums.
declare @TheName char (8)
,@Cmd char (500)
select @TheName = (select max(LastRunDate) from NSP_Analysis_Dates)
select @Cmd = 'CREATE TABLE `NSP_Analysis_' + @TheName + '` (`TYPE` VarChar (200) NULL, `VENDOR_NO` VarChar (10) NULL, `VENDOR_NAME` VarChar (50) NULL, `AMOUNT_CLAIMED` Currency NULL, `REGION` VarChar (100) NULL, `CLUSTER` VarChar (100) NULL, `PLANT_CODE` VarChar (15) NULL, `PLANT_NAME` VarChar (50) NULL, `DATE_OF_SERVICE` DateTime NULL, `STATUS` VarChar (200) NULL, `TICKET_NO` VarChar (20) NULL, `NOTES` VarChar (255) NULL, `DATE_ENTERED` DateTime NULL, `AuthorisedByName` VarChar (50) NULL)'
sp_executesql @Cmd
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
February 5, 2008 at 1:54 pm
You're trying to execute a SQL Server stored procedure in an Execute SQL Task where the connection is an Access database? That's not going to work.
Here's one way to do what you want:
1) Create a global variable that will store the dynamic part of the table name. I'm calling it "DynamicTableName"
2) Create a new Execute SQL task. It's SQL statement will be "SELECT MAX(LastRunDate) FROM NSP_Analysis_Dates". Set up an Output parameter that will store the value returned by this query in your "DynamicTableName" Global Variable.
3) Create an Active X Script task. This will populate the SQL Statement of your original Execute SQL Task. I'm assuming that your Execute SQL Task is "DTSTask_DTSExecuteSQLTask_1". Add this code to the Active X Script task:
Function Main()
Dim strTableName
Dim objSQLTask
strTableName = DTSGlobalVariables("DynamicTableName").Value
Set objSQLTask = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask
objSQLTask.SQLStatement = " CREATE TABLE `NSP_Analysis_" & strTableName & "` (`TYPE` VarChar (200) NULL, `VENDOR_NO` VarChar (10) NULL, `VENDOR_NAME` VarChar (50) NULL, `AMOUNT_CLAIMED` Currency NULL, `REGION` VarChar (100) NULL, `CLUSTER` VarChar (100) NULL, `PLANT_CODE` VarChar (15) NULL, `PLANT_NAME` VarChar (50) NULL, `DATE_OF_SERVICE` DateTime NULL, `STATUS` VarChar (200) NULL, `TICKET_NO` VarChar (20) NULL, `NOTES` VarChar (255) NULL, `DATE_ENTERED` DateTime NULL, `AuthorisedByName` VarChar (50) NULL)"
Set objSQLTask = Nothing
Main = DTSTaskExecResult_Success
End Function
4) Execute the tasks created in steps 2 & 3. Open the original Execute SQL task. You'll see that it's been populated with an actual SQL statement that Access will understand. Execute this task to create your table.
February 5, 2008 at 2:17 pm
Erik,
You sir, are a star!!
Many thanks
Stuart
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply