Tamara, what Tom said about SQL Server not recognizing the sas dataset is correct. However, your error is happening before SQL Server even gets that far. The only way to create a SQL Server table on the fly is by using SELECT INTO. For example, consider this
SELECT TOP 10 *
INTO #test
FROM sysobjects
This will create a temp table on the fly, copying the structure from the sysobjects table. Now, even if you changed your create statement to something like the above, you would still encounter the problem of SQL Server not finding your SAS data. One option would be to use SAS to put a flat file somewhere in a directory that is accessible to SQL Server, let's call it 'c:\sql_server_share\yourfile.txt'. Now, you will have to create a procedure on your SQL Server that will be something like this...
CREATE PROCEDURE spImportSasFile AS
IF OBJECT_ID('dbo.NewTable','u') IS NOT NULL
DROP TABLE dbo.NewTable;
SELECT *
INTO dbo.NewTable
FROM OPENROWSET
(
'MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\sql_server_share;',
'SELECT * FROM yourfile.txt'
);
Once you have created the procedure, your ODBC call will be something like this...
execute (exec spImportSasFile) by odbc;
Now, this is wholly dependent upon either your permissions to create this procedure on your SQL Server, or the DBA's willingness to help. One other thing, in order to allow the reading in of external data sources, you will have to set some configuration options. If you can execute the below, it should work for you...
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries',1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'remote query timeout (s)',28800
GO
RECONFIGURE WITH OVERRIDE
GO
There is a reason why the first two options are set to not allow ad hoc access to external data, so you should check with your DBA to discuss. I hope this helps.
Greg
