September 23, 2008 at 4:53 am
I have written the following query, that loops through some servers and gathers space information. But I cannot get the dynamic openrowset part to work. I just get an error saying this is not a valid identifer. Can anyone help ?
DECLARE @ServerName varchar(255)
DECLARE @sql as varchar(6000)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT sname
FROM dbo.tbl_serversT
OPEN c1
FETCH NEXT FROM c1
INTO @ServerName
WHILE @@FETCH_STATUS = 0
BEGIN
Set @sql = 'SELECT a.* FROM OPENROWSET("SQLOLEDB","' + @servername + '";"NOTREALUSER";"NOTREALPASSWORD","SET FMTONLY OFF;EXEC master.dbo.xp_fixeddrives")AS a;';
Exec @sql
FETCH NEXT FROM c1
INTO @ServerName
END
CLOSE c1
DEALLOCATE c1
September 23, 2008 at 8:14 am
I just get an error saying this is not a valid identifer.
Can you post the exact error message please?
Edit:
Apologies, the error is thrown because it's looking for each SELECT statement to be an sp or executable object; wrap your variable in brackets like so:
EXEC (@sql)
😀
September 23, 2008 at 8:16 am
Msg 203, Level 16, State 2, Line 20
The name 'SELECT a.* FROM OPENROWSET("SQLOLEDB","NOTREALSERVER";"NOTREALUSER";"NOTREALPASSWORD","SET FMTONLY OFF;EXEC master.dbo.xp_fixeddrives")AS a;' is not a valid identifier.
September 23, 2008 at 8:23 am
I have changed the code to Exec (@sql) but I now get:
Incorrect syntax near 'SQLOLEDB'.
September 23, 2008 at 8:25 am
Hi Jaaba
Check the surface area configuration on your server, you have to enable to Open rowset option on the server, you will get this problem when this is not enabled.
Cheers
🙂
September 23, 2008 at 8:29 am
September 23, 2008 at 8:29 am
OpenRowset is configured on the server.
September 23, 2008 at 8:34 am
Changed all quotes(") to apostrophes (')
still get:
Incorrect syntax near 'SQLOLEDB'.
September 23, 2008 at 8:39 am
Jabba, can you use PRINT @sql to show a line of code before execution and post that here? I think you need to double up the apostrophes within the string.
September 23, 2008 at 8:56 am
If I change all the quotes (") to apostrophes (') it comes back with the SQLOLEDB ERROR when I change to Print @sql
However if I run my original post with the quotes I get:
SELECT a.* FROM OPENROWSET("SQLOLEDB","NOTREALSERVER";"NOTREALUSER";"NOTREALPASSWORD","SET FMTONLY OFF;EXEC master.dbo.xp_fixeddrives")AS a;
as the results
September 23, 2008 at 9:04 am
Try copying the below and see if it works:
Set @sql = 'SELECT a.* FROM OPENROWSET(''SQLOLEDB'',''' + @servername + ''';''NOTREALUSER'';''NOTREALPASSWORD'',''SET FMTONLY OFF;EXEC master.dbo.xp_fixeddrives'')AS a;';
I've tried it using the linked servers on my machine (and changed login details!) and it worked okay.
September 23, 2008 at 9:21 am
I get this message:
Could not find server 'SELECT a' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.#
Ultimately I would like to avoid any solution that requires linked servers, as it would mean I would have to create thirty old linked servers.
September 23, 2008 at 10:37 am
The following code worked for me:
DECLARE @ServerName varchar(255)
DECLARE @sql as varchar(6000)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT name
FROM dbo.tbl_serversT
OPEN c1
FETCH NEXT FROM c1
INTO @ServerName
WHILE @@FETCH_STATUS = 0
BEGIN
Set @sql = 'SELECT a.* FROM OPENROWSET(''SQLOLEDB'',''' + @@servername + ''';''NOTREALUSER'';''NOTREALPASSWORD'',''SET FMTONLY OFF;EXEC master.dbo.xp_fixeddrives'')AS a;';
Exec (@sql)
FETCH NEXT FROM c1
INTO @ServerName
END
CLOSE c1
DEALLOCATE c1
HTH
MJ
September 24, 2008 at 2:37 am
That's brillant. Thank you all for your help.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply