dbConnection.Execute fails after I installed sql 2008

[ck9663]

Hi guys,

I have this code working when my local machine only have SQL2005. There is a need for me to install SQL2008, so I did. I now have two versions of SQLServer and now it's not working.

This is a VBA Script in my OUTLOOK that reads my incoming email, parse the subject and execute a stored procedure to another server, not my (local) server.

Could you guys help me out on this

Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
   Dim arr() As String
   Dim sqlStr As String
   Dim i As Integer
   Dim ns As Outlook.NameSpace
   Dim itm As MailItem
   Dim m As Outlook.MailItem
   Dim dbConn As ADODB.Connection
   Set dbConn = New ADODB.Connection
   Dim rs As Recordset
 
   dbConn.ConnectionString = "DRIVER={SQL Server};SERVER=(local);DATABASE=Compositions;UID=;PWD=;"
 
   dbConn.Open
 
   Set ns = Application.Session
   arr = Split(EntryIDCollection, ",")
   For i = 0 To UBound(arr)
      Set itm = ns.GetItemFromID(arr(i))
      If itm.Class = olMail And Trim(itm.SenderName) = "ThisSender" And Trim(itm.SenderEmailAddress) = "thisemail@address" Then
 
         sqlStr = "declare @sqlstatement varchar(max) "
         sqlStr = sqlStr & " set @sqlstatement = 'exec StoredProcName ''" & Mid(itm.Subject, 32, 3) & "'', ''" & Mid(itm.Subject, 36, 3) & "'''"
 
         sqlStr = sqlStr & " exec (@sqlstatement) at [LinkedServerNameHere]"
 
         Set rs = dbConn.Execute(sqlStr)
 
         dbConn.Close
 
         Set dbConn = Nothing
         Set rs = Nothing
 
      End If
   Next
   Set ns = Nothing
   Set itm = Nothing
   Set m = Nothing
End Sub
 

The dbConn.State = 1 which means I am connected. But the record state = 0.

Any idea?

Thanks,

~~ CK

[NeoPa]

Hi CK.

Can you say if/where it crashes and any error messages you see?

My first tip, before we get going properly, is to ensure that all variables of types which have DAO and ADODB flavours, should be defined explicitly as such. I expect you would want (in line #10) :

Dim rs As ADODB.Recordset

[ck9663]

Thanks NeoPa.

It's really strange. This code does not return anything and seems to be not working:

Set rs = dbConn.Execute(" declare @x as int; set @x = 1; select @x as x")
 

But this works:

Set rs = dbConn.Execute(" declare @x as int; select @x as x")
 

Any idea?

[NeoPa]

Particularly strange as it appears to be T-SQL relative CK :-S

I'm afraid I can't help there much as I've lost all my SQL servers now and have nothing to test on. Do I remember though that there is an alternative command for SET in T-SQL. I thought it was sometimes replaceable with SELECT. I have to admit my recollection is pretty fuzzy though. My T-SQL was always heavily dependent on the Books Online help system. I'm afraid I did far too little of it in my job to get familiar with much of it :-(

[ck9663]

Thanks Neo. Yes you are right, as an assignment statement, SET can always replace SELECT, but not the other way around.

The T-SQL is running fine if I paste it in the server. It is also the same connection technique am using in a Classic ASP application and it's running fine. So it really puzzles me.

Anyway, thanks for the help. I'll try to figure this out :)


~~ CK

[NeoPa]

I'm sorry I couldn't be more help CK. I remember you often helped me when I needed SQL Server help.