Can anyone please help me with this sql. I get an error as

[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data
source name not found and no default driver specified]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005:   ].

when i try to run this as a dynamic sql...

SET @SQL = N' SELECT A.* INTO dbo.Test FROM OPENROWSET( ''MSDASQL'',
''DRIVER={SQLServer};SERVER=' + @ServerName + ''' ,''USE ' + @name + ' SELECT
a.name, a.fileid,
a.filename, b.groupname, a.size * 8 AS Size, [maxsize]
= CASE a.maxsize WHEN -1 THEN 0 ELSE a.maxsize * 8
END, a.growth * 8 AS growth, a.status,  b.status
groupstatus, FILEPROPERTY ( a.name, ''''SpaceUsed''''
) * 8 AS UsedSpace, ''''' + @name + '''''
database_name, ''''' + CAST( @SampleDate AS
VARCHAR(30)) + ''''' SampleDate, ' + CAST (@Server_ID
AS VARCHAR(12)) + ' ServerID FROM ' + @name +
'.dbo.sysfiles a LEFT JOIN ' + @name +
'.dbo.sysfilegroups b on b.groupid = a.groupid''
        AS A'

Any suggestions will be greatly appreciated.

TIA

First of all - what! a! mess!

In the lack of appropriate DDL I suspect the flaw is a missing bracket:

<snip> a.groupid''
       ) AS A'


       ^
But this might be just the top of the iceberg...


ML

Since the values of your variables are unknown to others in this newsgroup,
it is somewhat hard to guess what the exact issue is. Instead of executing
the script use the PRINT statement to see if it properly conform to a valid
t-SQL statement and make sure if the server & the database are available.

Create a stored procedure and distribute it to each server, and schedule it
to populate a single central server, instead of having the central server
poll all of the servers dynamically.

I was going to be less eloquent, but ML said it best: what a mess!

Aaron Bertrand [SQL Server MVP] wrote:
Show quoteHide quote

sorry but i dont think if you look closely its so difficult to be messy.
whats the point of running same procedure from all servers when you can
control it from one server it. The actual question here is if anyone has
tried using MSDASQL successfully to access data from a remote server which is
set up as linked server from where you want to run this script.
As regards to variables i know what needs to go there i am not expecting a
result set here so i dont expect anyone to run and see this query..

Why don't you use the SQLOLDB provider?

Can I avoid using a cursor
DECLARE CURSOR ON EXEC... (Sybase does it, so why cant SQL Server?
Aaron, Itzik - about the MIN Function
IDENTITY_INSERT
Conditional select into on temp table