July 21, 2009 at 4:14 am
Hello,
I didnt know whether to put this in admin or developer, I have a VB script, not written by me and I dont understand VB. It runs on all SQL Servers regardless of edition, but I have come across one that it will not run on, despite working on other servers with the same build. Its a SQL 2000 Server SP3.
The script gathers various bits of info from all the databases so its read only, normaly it runs without problem but this time I am getting login errors, no major details to be had, just that when I run the scripts it says it is unable to connect, when I look in the Event viewer, I can see that I have a login sucessful entry, but the very next entry is login failed (both with my username). I have tried adding myself to various things in the local security policy, and have admin rights on the local machine and have sysadmin rights on the SQL Server, so I just cant figure out what is standing between me and this script working. I run the script from the server itself via RDP. The script has also worked on servers of the same build within the same network, so I am sure it has nothing to do with firewalls etc and the login info backs that up as far as I can see.
Sorry if this is a bit vague, I'm happy to supply more info. I have tried to sort this out myself, I have looked at every possible posting on google, none of which seem to apply to me and tried all possible of combinations of the event id, but still I have drawn a blank. The script is ok, because if runs fine on other servers, there is just something about this one.
Can anybody give me an more places or ideas as to why this may not be working?
Any help or suggestion of new avenue of investigation would be appreciated.
Thanks for reading,
D.
July 21, 2009 at 6:08 am
I would start investigating from the basics: does the server respond to connection attempts?
Try using a tool such as OSQL.EXE to test connectivity from the client machine you are running the script.
If that computer doesn't have SQL Server client tools installed, you can copy OSQL.EXE from the server machine and run:
OSQL -E -Sservername
If it doesn't respond I would check:
1) DNS entries (does the server ping?)
2) Active protocols on server settings
3) Firewall
If it responds and you are not able to connect with VB, check your connectionString. You can post it here if you like.
Gianluca
-- Gianluca Sartori
July 21, 2009 at 6:26 am
Hello thanks for getting back.
does the server respond to connection attempts? Yes.
Try using a tool such as OSQL.EXE to test connectivity from the client machine you are running the script.
I'm running the script on the server itself no client involved.
1) DNS entries (does the server ping?) Yes, this was one of the first things I checked. Also reverse ping works as well.
2) Active protocols on server settings There is named pipes and TCP\IP
3) Firewall - The script works fine on the other servers behind the same firewall.
If it responds and you are not able to connect with VB, check your connectionString.
I am not sure how to do this Im afraid.
Thank you,
D.
July 21, 2009 at 8:32 am
You should have a line in your script such as:
Set someVariableName = createObject("ADODB.Connection")
That "someVariableName" is your connection object.
At some point in the script you should have something like:
someVariableName.connectionString = "yourconnectionString"
Can you post here the value of "yourconnectionString"?
If you are unable to locate that connectionString I'm afraid there's no way to help you.
-- Gianluca Sartori
July 24, 2009 at 4:12 am
Hello,
Firstly, thank you for your help on this. I think this maybe what you are looking for...
Set Shell = CreateObject("Wscript.Shell")
Set System = CreateObject("Scripting.FileSystemObject")
These are the first two lines that look like what you are asking, but I couldnt be sure. I dont know if its useful but this is the bit of code that brings up the unable to connect error...
objRecordset2.MoveNext
Loop
else
objExcel.Cells(i, 1).Value = instance
objExcel.Cells(i, 2).Value = "Unable to Connect"
i = i + 1
end if
objRecordset2.close
I dont know anything about VB so if these are the wrong lines, I'll try to find the right ones.
Thanks again,
D.
July 24, 2009 at 4:41 am
I'm sorry, these are not the correct lines.
Try looking for "adodb.connection".
Why not posting the whole script? You can attach it to the post.
-- Gianluca Sartori
July 29, 2009 at 10:06 am
Hi there,
I sent you a PM on this, did you receive it ok? I'm not sure if it went through or not.
Regards,
D.
July 29, 2009 at 10:53 am
Not yet. I will check tomorrow.
-- Gianluca Sartori
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply