Having Trouble Connecting To Sql Server

[bretterson]

We recently moved all of our things to some new servers, and I'm trying to get VG working on our new servers.

 

It all seems to be working right, except that I can't get it to connect to our new SQL Server db. I tried just updating the connection string, but that didn't work. I also tried creating an ODBC connection and just specifying it as the Data Source, but that didn't work either.

 

I was able to get it to work for the small MySQL db we have for some legacy applications, but it doesn't want to work for the scripts that use SQL.

 

The first thing the script does is asks for an id. Then it saves it to our call log in the db.

 

Set cn = CreateObject("ADODB.Connection")
cn.Open "Data Source=MyDataSource"
set rs = CreateObject("ADODB.Recordset")
set vg = CreateObject("VoiceGuide.CommandLink")

''If "$RV[callsaved]"= "Y" then
''  'Do not save
''   vg.Admin_TraceLogAdd 0, 10, "YSaveCallLog: " & "Bypassed"
''else
''   sDateTime = GetDate()

  vg.Admin_TraceLogAdd 0, 10, "YSaveCallLog: " & "SAVED"
  sQuery="INSERT INTO callLog (PhoneNumber, Message, CreateTime, LastCallTime, CallStatus, ScriptName) VALUES ('','" & "$RV[AskforId]" & "','2012-01-01','2012-01-01','','frstops.vgs')"
  vg.Admin_TraceLogAdd 0, 10, "YSaveCallLog-Q1: " & sQuery
  cn.execute sQuery

 

I hardcoded the date in case the GetDate() function was throwing an error, but that doesn't look like it's the problem. The callLog is never affected by this line when we make a call. Also I searched through the log file for "YSaveCallLog" and "SAVED". I couldn't find either one, but it's been a while since I've had to look into VG logging, so I may have done that wrong.

 

Any ideas?

 

Thanks

[SupportTeam]

Perhaps the script is encountering problems at this line:

 

cn.Open "Data Source=MyDataSource"

 

I think the intention was that MyDataSource is a ODBC data source, which would need to be defined on the new machine.

 

Usually the connection strings look more like this:

 

cn.Open "Driver={SQL Server}; Server=myserver; Database=mydb; UID=sa;

 

cn.Open "Provider=SQLOLEDB;Server=DBSERVER1;UID=user;PWD=user;Database=Ads"

 

also see: http://www.connectionstrings.com/

 

so you can try alternate ways of connecting to the database using the connect strings similar to above.

 

 

 

Suggest you test the database connectivity from VBScript first by running a .VBS file directly. This would be quicker then calling into VoiceGuide to run it.

(VoiceGuide saves VBScripts that are ran in the \temp\ subdirectory)

[bretterson]

Yes, it is an ODBC Connection, and it is set up on the machine.

 

Before I switched to the ODBC it looked like your first one.

 

I'll try running a VBS file to see if I can get it to work.

[bretterson]

I figured it out. Thank you very much for the help.

 

It turned out there were a couple problems. The first was that when I commented out the if else in the code I posted, I forgot to comment out the end if.

 

Also when I used a connection string like "Driver={SQL Server}; Server=myserver; Database=mydb; UID=sa; Pwd=password;" there was a typo in the password I hadn't noticed.

 

The ODBC connection should have worked however. When using an ODBC connection to a SQL db do we still need to put "Driver={SQL Server};"?

[SupportTeam]

When using an ODBC connection to a SQL db do we still need to put "Driver={SQL Server};"?

Shouldn't be necessary, so not sure why the ODBC approach would not work.