Hi!
I'm trying to use the Opendatasource command within a stored procedure to access data from a spreadsheet on another networked drive. The SQL Server Service account does not have rights on this networked drive, which is why the command won't work. They don't want to set that up for us. The command works fine when the spreadsheet is located on the server's C: drive, so I know I have the correct syntax for the command. The issue seems to be just accessing data across the network. And yes, I tried both the mapped drive and the UNC location of the spreadsheet.
I hear there is a way of creating an ADO connection string to establish the connection, but I have no clue how to do this within a stored proc. I do know how to write the connection string in VBA, but I don't know how to put that into a stored proc so that it will establish the connection and then execute the OpenDataSource command - or allow me to do a Select on the spreadsheet.
Alternatively, is there a way to pass an NT login and password within the Opendatasource command? I can't find an example of that if it happens to be possible.
Any suggestions or sample connection strings would be greatly appreciated.
Oh, just to let you know, using DTSRun isn't an option - they won't set it up. Nor is using the "addlinkedserver" command, which the user doesn't have the rights to execute.
Thanks!!
Karen Grube
kgrube@ffres.com
klgrube@aol.com
I'm trying to use the Opendatasource command within a stored procedure to access data from a spreadsheet on another networked drive. The SQL Server Service account does not have rights on this networked drive, which is why the command won't work. They don't want to set that up for us. The command works fine when the spreadsheet is located on the server's C: drive, so I know I have the correct syntax for the command. The issue seems to be just accessing data across the network. And yes, I tried both the mapped drive and the UNC location of the spreadsheet.
I hear there is a way of creating an ADO connection string to establish the connection, but I have no clue how to do this within a stored proc. I do know how to write the connection string in VBA, but I don't know how to put that into a stored proc so that it will establish the connection and then execute the OpenDataSource command - or allow me to do a Select on the spreadsheet.
Alternatively, is there a way to pass an NT login and password within the Opendatasource command? I can't find an example of that if it happens to be possible.
Any suggestions or sample connection strings would be greatly appreciated.
Oh, just to let you know, using DTSRun isn't an option - they won't set it up. Nor is using the "addlinkedserver" command, which the user doesn't have the rights to execute.
Thanks!!
Karen Grube
kgrube@ffres.com
klgrube@aol.com
