Another SQL Stored Procedure Question

Thanks for the help with the last question... I finally got it to work... I have a new problem tho with the same setup -

I need to create a table in the Access97 database and then populate it with SQL Server2k data. I'm using a SELECT ... INTO statement (which works fine in the SQL Enterprise Manager QRY thingy (I'm soooo technical) but it's not working when the stored procedure is called. The Access97 database is set up as a linked server, I'm pulling the database name in thru a parameter along with the condition. Maybe one of you can see something I don't with this code?

CREATE PROCEDURE dbo.usp_PutData @.DbName nvarchar(20), @.State nvarchar(4), @.AirType nvarchar(10)
AS

declare @.QueryIs as varchar(8000)

select @.QueryIs = Case upper(@.AirType)
when 'NFDC'then "SELECT (FldName1, FldName2, FldName3, FldName4, FldName5, FldName6, FldName7) INTO " + @.DbName + "...Facility FROM tbl_NFDC_Facility WHERE (Assoc_State = '" + @.State + "')"
end

Select @.QueryIs, @.DbName
exec (@.QueryIs)
GO

Any and all help is greatly appreciated!Did you cut and paste this code directly from MSSQAT (Microsoft SQL Server Query Analyzer Thingy)? Your use of double quotes is a syntax error. You have to use single quotes, and tripled single quotes to represent an embedded single quote.

Also, you use too many dots qualifying your table name.

@.DbName + "...Facility

should be

@.DbName + "..Facility

otherwise SQL Server will look be looking for a server named after your database. A fully qualified table reference looks like this:
[ServerName].[DatabaseName].[Owner].[Table]

Also, be aware that there are some connection settings that may have different defaults in Query Analyzer than other DB interfaces. For instance, SET CONCAT_NULL_YIELDS_NULL { ON | OFF } defaults to ON for SQL Query Analyzer, but defaults to OFF in Crystal reports.

blindman