Help with a sql query
-
I have a sql statement wherein I am trying to query on sysfiles and sysfilegroups table on all databases on all servers. the statement i am using is
IF EXISTS (SELECT * FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'FileGroup')
DROP TABLE dbo.FileGroup SET @sql =
N'SELECT A.* INTO dbo.FileGroup FROM OPENROWSET( ''MSDASQL'', ''DRIVER={SQL
Server};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'PRINT 'GETTING FILEGROUP INFORMATION FOR ' +
@name
select @sql
exec sp_executesql @sql
when excuting this I get an error as
OLE DB provider 'MSDASQL' reported an error. Server: Msg 7399, Level 16, State 1, Line 1
[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: ].
AM i mising any settings on my m/c or have i gone wrong with my sql statement. Can anyone please help?
TIA
-
You'd probably be better off using SQLOLEDB provider rather than MSDASQL (which requires a ODBC DSN)
A.J.
DBA with an attitude -
However make sure that your registry settings for that particular OLEDB Provider have a value of
DisAllowAdHocAccess = 0
If this is non-existant it will not allow adhoc access (i.e. OpenQuery, OpenRowset). If this setting is there but set to 1 you'll also get ad-hoc access denied. (error 7399)
This setting is under different areas depending on whether or not you have an instance name.
If you do not have an instance name it will be under:
HEY_Local_Machine\Software\Microsoft\MSSQLServer\Providers\<ProviderName>
<ProviderName> will be either MSDASQL (if you choose to use that) or SQLOLEDB if you choose to use the OLE DB Standard.
If you do have an instance name the registry path will be:
HEY_Local_Machine\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Providers\<ProviderName>
If the DisAllowAdHocAccess setting is not there add it and set it to 0 (DWORD)
*** Also make sure you are connected using SQL Authentication *** Using Windows Authentication, you will likely encounter issues with "Double-Hop Authentication" See http://support.microsoft.com/?id=241267
A.J.
DBA with an attitude -
Thanks but somehow i dont see any instance under my registry. it shows only as
HEY_Local_Machine\Software\Microsoft\Microsoft SQL Server\80. any idea where i am going wrong??
-
Also when using SQLOLEDB I am now getting
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid connection string attribute]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ].
-
Look under \\... Microsoft\MSSQLServer\Providers....
A.J.
DBA with an attitude -
I checked the registry and all looks fine. I now tried using SQLOLEDB
SELECT a.* FROM OPENROWSET( 'SQLOLEDB', 'dbname' ,'select * from dba.dbo.ServerTypes')AS a
and the error shows up as
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid connection string attribute]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ].
any other setting that needs to be done. Please help!!!
TIA
-
A couple things to ensure -
1. You are logged in using SQL Authentication
2. Your SQL Server's service account is using a domain account (with access to the target servers in the OPENROWSET). The service account can not be set up to be using the "system account"
A.J.
DBA with an attitude -
Also - make sure you include the login and password in the openrowset query
You had:
SELECT a.* FROM OPENROWSET( 'SQLOLEDB', 'dbname' ,'select * from dba.dbo.ServerTypes')AS a
Try
SELECT a.* FROM OPENROWSET('SQLOLEDB','ServerName';'sa';'MyPassword,
'SELECT * FROM dbname.dbo.ServerTypes) AS a
A.J.
DBA with an attitude -
Not sure how do i check for this
Your SQL Server's service account is using a domain account (with access to the target servers in the OPENROWSET). The service account can not be set up to be using the "system account".
also i dont want to hardcode the user id and password as its different for diff servers . Can i have something like a trusted connection?
-
You can only have a "trusted connection" by not using OPENROWSET - instead you'd have to create a linked server.
To check/edit the service account just go to properties in Enterprise Manager for that server and go to the Security tab.
A.J.
DBA with an attitude -
You can only have a "trusted connection" by not using OPENROWSET - instead you'd have to create a linked server.
To check/edit the service account just go to properties in Enterprise Manager for that server and go to the Security tab.
A.J.
DBA with an attitude
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply