Trouble with SQL Server Connection ...

[jce1975]

Hello,

I am using Coldfusion to call a .vbs file that creates a Powerpoint file using information it pulls from a database. It doesn’t work. Here is the code I am using to execute the .vbs file:

-------------------------------------------------------------------------------
<cfexecute name="C:\WINDOWS\system32\cscript.exe"
arguments="#rootDirectory#\testing.vbs"
outputfile="#docsDirectory#\error.log">
</cfexecute>
-------------------------------------------------------------------------------

I am not sure why the powerpoint file isn’t being generated, but the powerpoint file is generated when I comment out this code (code in the .vbs file):

-------------------------------------------------------------------------------
objConn.Open "DSN=wrd;UID=wrd_user;Password=********"
-------------------------------------------------------------------------------

Here is some more of the code from the .vbs file:

-------------------------------------------------------------------------------
Dim rootDirectory
rootDirectory = "D:\pma"

'Used for recordset object
Dim objConn, objRS

' Define constants from ADOVBS.INC
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adCmdText = &H0001

' Create and setup connection object
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=wrd;UID=wrd_user;Password=********"

Set ppt = CreateObject("Powerpoint.Application")
ppt.Visible = True
ppt.Presentations.Open rootDirectory & "\ppt\PMA.ppt", True, False, True
ppt.Presentations("pma.ppt").SaveAs rootDirectory & "\docs\PMA_TEST.ppt"
ppt.Presentations("PMA_TEST.ppt").Close
'Close Objects and powerpoint
ppt.Quit
Set ppt = Nothing
-------------------------------------------------------------------------------

I am using a Windows 2003 Server, Microsoft IIS, Coldfusion MX 6.1 and a SQL Server 2000 database. The SQL Server database is on a separate server. I am curious if I have to specify exactly where the server exists? I tried placing “SERVER=myServerName” into that line of code with no luck. I know that the DSN, UID and the PASSWORD are correct because I was able to establish a connection the database by another means. Any help would be greatly appreciated! Thanks.

 

[NoCoolHandle]

You seem to be missing the provider argument..

with an odbc dsn add provider=msdasql

ie
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "[red]Provider=msdasql;[/red]DSN=wrd;UID=wrd_user;Password=********"

Also, I hate going through another object layer if it can be avoided.. I would tend to replace the dsn with a sqlserver type connection string..


ie
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "[red]Provider=Sqloledb;server=YourServerName;Database=YourDatabase[/red];UID=wrd_user;Password=********"

You might even find it runs faster

Rob

 

[jce1975]

Hello Rob,

Thank you very much for your response! I tried your two suggestions with no luck? I am really confused with this problem. Are there any settings on the database server that could be preventing the connection from occurring other than the UID or Password? I have done a lot of research and can't seem to find a solution?

James

 

[NoCoolHandle]

What is the errormessage?

 

[NoCoolHandle]

Also for the heck of it try building and running the following script (changing the red vaues to match your server) - it should give you a messagebox for each table in your db..
If it doesn't work, try running it in the "master" database.
It might be a simple permissions issue, but....



The Listtables.vbs file

dim cn,rs
set cn=createobject("adodb.connection")
set rs = createobject("adodb.recordset")
cn.open "Provider=sqloledb;server=[red][i]YourServer[/i][/red];database=[red][i]YourDatabase[/i][/red];User ID=[red][i]YourLogin[/i][/red];Password=[red][i]YourPassword[/i][/red]"
rs.open "Select name from dbo.sysobjects where type='u'",cn
do while not rs.eof
  msgbox rs(0).value  
  rs.movenext
loop
rs.close
cn.close
set rs = nothing
set cn = nothing

also you might want to try a trusted connection (nt login)
run the following script using you server name where you see the (local)

dim cn,rs
set cn=createobject("adodb.connection")
set rs = createobject("adodb.recordset")
cn.open "Provider=sqloledb;server=(local);database=Master;integrated security=sspi"
rs.open "Select name from dbo.sysobjects where type='u'",cn
do while not rs.eof
  msgbox rs(0).value  
  rs.movenext
loop
rs.close
cn.close
set rs = nothing
set cn = nothing

 

[jce1975]

I am currently running the .vbs file through Coldfusion. The .vbs file is then interpreted by the cscript.exe file. So, I never see any errors because Coldfusion doesn't know what is happening inside that .vbs file. Can you reccommend a decent VBscript compiler that is free or a trial version? I am assuming I would be able to see any error messages if I build and run it ...

Thanks again for so much help!

James

 

[NoCoolHandle]

James, chances are that you have a builtin tool for running vbscripts (comes with ie)

Just double click a vbs file and it will run.

Did you try either of the other scripts I added above?
They are trying to just make a very clean connect and query data that the public group could see.

If you did't get an error look at the connectionstring there and change yours to echo that.

I am very suspicous of a permission issue.

Rob

 

[jce1975]

Rob,

I double clicked on the .vbs file and it started displaying lots of msgboxes with table names. All msgboxes just had "OK" buttons to press. Then, I got an error message when saying:

Script: X:\testing.vbs
Line: 161
Char: 1
Error: PowerPoint could not open the file.
Code: 80004005
Source: Microsoft Office PowerPoint

This error is odd since I am able to get the PowerPoint file to open when I comment out that line of code with the database connection and execute the .vbs file from Coldfusion?

I tried both of the suggestions you had above using Coldfusion to execute the .vbs with no luck?

Thanks,
James

 

[NoCoolHandle]

Oooh that would tend to indicate that you have a good connection to the database.

How does the pps file use the connection you just opened? That seems wierd to me that a vbs script would (somehow) pass a connection to the powerpoint slide and it looks implicit in the top code.

and why does the powepoint file open if you don't have a connection but doesn't if you have an open connection.....

No answers.. just questions.

Very wierd.

 

[jce1975]

Rob,

Everything is working now! Not sure why this didn't work before, but it works very well now! This is what I used:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Sqloledb;server=YourServerName;Database=YourDatabase;UID=wrd_user;Password=********"

Thank you so much for your help!

James