Click here to Skip to main content
15,793,452 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)

I have dozens and dozens of reports (run from a vbs script) that are sent on a daily basis. These reports get their data from 5 data servers. All these data servers are the same and I usually have the reports run from one or two of them. At times a server has issues and the data is not current so I need to move the reports to a different data server. The issue is maintaining 5 sets of reports. People often make changes to one report and not the others so when we have to change data servers, we are chasing our tails. My vision is to have one set of scripts/reports housed one one separate server (of course backups are done). But instead of changing each script and report to get their data from a particular server, I want to have a single text file that says "SERVER_1". When a report is run it reads the source text file before connecting and generating a report. When a data server is having an issue, all I have to do is change the text file to "SERVER_2". I am having trouble making this work. Here is what I did.

What I have tried:

I have this in my vbscript:

set databaseIntegration = createobject("adodb.connection") "provider=sqloledb;data source=" & sourceServer & ";initial catalog=database;integrated security=sspi;"

Prior to the above code I put this and it did not work. The error I get for this is (
Error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
) But when I hard code the server into that spot it works.

Dim sourceServer 
sourceServer = "C:\Users\me\Documents\"

I also tried this:

Set objFileToRead = CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\Users\me\Documents\server_source.txt",1)
sourceServer = objFileToRead.ReadAll()
Set objFileToRead = Nothing

And it still did not work. This is the error for this one. (
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Updated 18-Jan-23 3:47am

You said sourceServer is this:
sourceServer = "C:\Users\me\Documents\"

Then you tried to put that string in your connection string:
VBA "provider=sqloledb;data source=" & sourceServer & ";initial catalog=database;integrated security=sspi;"

You cannot do that. The connection string is expecting the name of a server, NOT A FILEPATH.

You have to come up with the DNS name of the SQL Server to connect to. If that's stored in a file, you have to write some code to read that file and get the server name out of it. You can then insert that name into the connection string.
Share this answer
We can't tell: we just don't have access to your network to see what servers are available, or to your text file.

So, it's going to be up to you.
Fortunately, you have a tool available to you which will help you find out what is going on: the debugger. If you don't know how to use it then a quick Google for "Visual Studio debugger" should give you the info you need.

Put a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why.
Look closely at what sourceServer contains when you try to connect: it would appear not to be what you expect.
And TBH, I'm a surprised that giving a folder as a datasource works - I'd suspect that your code doesn't quite look like you think when you hardcoded the string!

Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900