January 12, 2003 at 5:01 pm
Hi!
I hope someone can help. Where I work, none of us have Bulk Insert rights and none of us are allowed to write our own DTS packages, even though we're on an isolated Test server. They refuse to give us admin rights. Nor can we create linked servers.
However, we need to be able to import data from a wide variety of sources from a few different network drives. This includes Excel spreadsheets, Access databases, and flat files.
So I'm trying to use the OpenDataSource command. I have been successful at importing data using Opendatasource. However, I found that when I refer to the "C" drive - using Microsoft's BOL example, what it means the SERVER's "C" drive, not MY "C" drive. That's okay. All of our servers have network mappings to other drives. For example, the "G" drive is always mapped to the Accounting data drive on every server and workstation. But, when I try to access a file from a folder on the "G" drive, I get the same error I get when the file I'm looking for doesn't exist or is inaccessible. That is, I will have a command that says something like:
Select * from Opendatasource( . . .)
etc. and it will work, as long as the file (spreadsheet or text file) I'm specifying resides on the server's "C" drive. But, I can place the same file in a folder on the "G" drive, and change the location to "G" in the opendatasource command, and the command will fail. Oh, in case I didn't mention it, this command is being executed at the moment from the Query Analyzer, and I am connected using my Windows NT login. I have full access to the drives and folders where these files are located.
I've checked as best I can to see that the sql server service account has rights to the drives and folders, and it seems to. I've even tried using the UNC location rather than the mapped drive.
Any suggestions? The issue is just that "they" (our IT Department) are not letting us import data into SQL, and it's stopping our work completely. They're refusing because they really don't want our department's development efforts to succeed and they're doing everything they can to stop us. If I can get his command to work correctly across network drives, at least I can move forward. For example, can I specify my own login and password in the opendatasource command, just in case this does happen to be a rights issue? I'm not sure of the correct syntax for that, or if it's even possible.
The DBA I'm working with simply shrugs his shoulders and says "You'll have to code a connection string in C or Java." - as if they'd let me do that anyway! I'm really at a loss. I'm absolutely desperate to get past this roadblock.
Any help you can provide would be greatly appreciated.
Thanks!
Karen Grube
January 12, 2003 at 5:22 pm
Sounds like G is not accessible from the server and/or with those permissions. If you've got xp_cmdshell access you can try doing a 'dir' to see. If that turns out to be the case, Im guessing you wont them to change it. If you can, then we're back to troubleshooting the open which doesnt seem likely.
Workarounds then.
1) Put personal edition on your workstation (or wherever), set up a linked server, DTS, whatever you want, bring the data in. Then push the data to the real server. Licensing not an issue, you get personal if you have server licensed. If you do this, put an SA password in place, do backups of your stuff. Eventually they'll see it though...and where will you be? lol
2) Just do it via ADO. ADO will open Access and Excel (thats all OpenDatasource does anyway), loop through the data and insert. Lower profile, but slower than #1.
#3) Get them to do the package. Or write in on personal edition, give to them as a done deal.
#4) Get them to do the bulk insert - again, giving them the code may help.
I dont blame them for not giving you admin rights or restricting linked servers. Bulk insert and DTS - I'd rather the developer did it myself, why waste time having them explain it to me? Not only that, the last thing I want them is them doing #1 or #2, winding up with a slower, more resource intensive solution than if I gave them some help.
Before you go around, I'd make a try at getting some help. Most DBA's (me included) take a dim view of end runs, regardless of reason. Our job is to safeguard data and performance. Sometimes they go too far of course.
Curious to hear how it works out!
Andy
January 12, 2003 at 8:05 pm
Hi Andy!
Thanks for the quick response. DTS would be overkill for the little files/tables I have to import. I really want to do this directly from within the stored proc. There are several people who may have to maintain this code, and I want to keep all the code in one place, not in a stored proc and DTS. Nor do I want them to have to mess around with linked servers. I want to keep it simple. Just go out and get the data.
Is there syntax for the opendatasource statement that will allow me to specify the username and password for the path on which the file exists? If there is, could you please provide an examples.
Do you have an example of how to do this by setting an ADO connection? How do you execute an ADO command from within a SQL stored procedure? What would the syntax be? I know how to write an ADO connection string in VBA, but I'm not sure how to do this from within SQL.
Again, any examples or links to where I can see how to do this would be greatly appreciated.
Karen
January 12, 2003 at 8:27 pm
You can do ADO in a proc (I guess) using sp_oacreate, but its a lot of work, more than its worth if you ask me. I dont have a password example handy, hopefully someone else will. As far as code, I usually put stuff like this in a VB exe and run from a dedicated 'process' machine, put the code in source control.
Andy
January 12, 2003 at 10:59 pm
Hi!
I wasn't able to find any help in the SQL Server books online for the OACreate command. It certainly doesn't describe how to call an ADO connection string.
Any other suggestions? All I want to do is open a spreadsheet located somewhere OTHER than the server's C drive using the opendatasource command. I just can't figure out the correct syntax for a cross-network distributed query like this.
Please help!
Thanks!
Karen
January 13, 2003 at 6:36 pm
I've had the same problem. What's more is that the UNC path seems to work if the file is on the local machine but once you move to a different machine it no longer works.
The following works from my garyjo4 but not from garyjo even though SQL Agent runs under my account on both machines.
SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0'
,'Data Source="\\garyjo4\C$\Test.xls";User ID=Admin;Password=;Extended properties=Excel 8.0'
)...Sheet1$
I think I would create an external app that you can collect the data with and then push it onto the server. That way people can have the app on their machine and it can remotely push the data when it needs to.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 13, 2003 at 10:45 pm
Hi!
Thanks for replying! I think the whole problem on our network is that the SQL Server Service account doesn't have rights to any of the drives I need to get to. That's intentional. It may not even be in the local admin group of it's own server. I'm not sure, but I don't have the ability to check that. I'm told I can create a connection string through ADO that I can execute from within a stored procedure. I've never seen that, but I've asked our IT development manager to send me an example. That's what she's saying to do. I just don't know how. If I do learn this technique, I'll post it here. In the mean time, if you learn anything new, please let me know.
Thanks
Karen
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply