help with sql script

Question

Post reply

help with sql script

ishaan99

SSCertifiable

Points: 6261
More actions
July 7, 2005 at 12:53 pm

#90144

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''
&nbsp AS A'
Any suggestions will be greatly appreciated.
TIA

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Are you querying a REMOTE server? .. I mean @ServerName <> LOCAL

* Noel

ishaan99 SSCertifiable Points: 6261 More actions · Answer 2

Yes thats right. I want to run this query on all servers and i have all these set up as linked server from my local PC.

ishaan99 SSCertifiable Points: 6261 More actions · Answer 3

Does anyone have a better idea or solution for this...

noeld SSC Guru Points: 96590 More actions · Answer 4

Sorry I could not get back to you yesterday. I'll see if I can help you today!!

* Noel

ishaan99 SSCertifiable Points: 6261 More actions · Answer 5

Thanks Noel. Really appreciate that. Let me make it more clear. Problem comes in executing MSDASQL as thats when i get the error. I tried using SQLOLEDB and it works fine only thing is if i do a USE <DBName> before doing a select it fails... any further clarifications please let me know. Thanks

noeld SSC Guru Points: 96590 More actions · Answer 6

and why are you using "USE" if you can always prefix the dbname to the table name ?

* Noel

ishaan99 SSCertifiable Points: 6261 More actions · Answer 7

You are right But in the sql there is a statement "FILEPROPERTY ( a.name, ''SpaceUsed'' ) * 8 AS UsedSpace from DBName.dbo.sysfiles a "which basically should look thru the spaceused for each file on the database. this shows up as empty if i dont use a "Use DBName" as by default it checks for master databse and returns a value for it but for rest of the data files it returns null

ishaan99 SSCertifiable Points: 6261 More actions · Answer 8

Noel,

Finally i got it working. was afault from my end only. I had an extra space between UID and <userid> it should have been UID=<userid> and i had as UID= <userid> so heres my sql which i tried

SELECT a.* FROM OPENROWSET('MSDASQL',

'DRIVER={SQL Server};SERVER=qacssdb;UID=XXX;PWD=YYY', 'use DDD SELECT a.name, a.fileid,

a.filename, fileproperty(a.name,''spaceused'')

from DDD.dbo.sysfiles a'

) As a

thanks again for your help...

noeld SSC Guru Points: 96590 More actions · Answer 9

I Was able to doit with the OLEDB Provider!

NO NEED for USE

You can use : the following connection string!

openrowset('SQLOLEDB',

'SERVER=XX;UID=XX;PWD=XX;DATABASE=XX',

'YOUR QUERY AS IS!!')

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 10

OOps! Glad you solved it

Different than me but hey good for both of us then!

* Noel

ishaan99 SSCertifiable Points: 6261 More actions · Answer 11

Thanks Noel. Another way of doing it. Appreciate your help...