Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Cannot get SQL code to work with @variables when passed through Excel

  Asked By: Pamela    Date: Jan 19    Category: MS Office    Views: 3582
  

I've created an object that users can enter SQL queries into and
then, via ODBC, pass that query to a SQL Server which processes the
query and then returns the results to a specified location in the
workbook....

Everything works great EXCEPT when I try to pass through queries
with variables. I'm at a loss because I know the query syntax is
correct, the query works from every other query editor/pass-through
tool I've tried. This tells me the problem most likely has
something to do with Excel and/or VBA.

For those of you who don't use SQL, SQL variables have the following
format:

'@' + some combination of characters

for example @variable1 would work as a variable

I get the feeling it has something to do with the '@' but I don't
know that for certain. I've tried various combinations of chr(64)
in place of the potentially offensive '@' with no luck.

Note for the SQL savvy, the solution for standard variables is to
find/replace prior to the pass-through execution but I'm trying to
work with table variables created by the SQL server.

Any thoughts would be appreciated.

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Agatha Miller     Answered On: Jan 19

I call Sybase stored procedures regularly. They use @ at the start of
parameter names. Works fine. The @ is simply @. I doubt that SQL Server
will be any different.

How do you know the query  syntax is actually correct? My bet would be that
it isn't.

You absolutely need to put the whole SQL statement into a string variable,
and inspect it (e.g. with a MsgBox call). Then, pass  that string variable
to SQL Server.

I expect that you will find that the SQL being passed is wrong. Typical
problems include: missing spaces; numeric variables  put into the statement
as names, rather than as values; missing quotes around string values;
missing commas. Looking at the code  that produces the SQL statement is not
the best way to find these types of problems - looking at the actual SQL
statement you are sending to the DB engine is the best way.

Once your SQL is working correctly, comment out the MsgBox call. (Don't
delete it - you'll just need it again, next time you change the query.)

 
Answer #2    Answered By: Sonya Flores     Answered On: Jan 19

Well I can say for certain the syntax  is correct because

1) the error I receive is "Run-time error '1004': Application-
defined or object-defined error" not a sql  syntax error and
2) the way the process works  is that I create the queries  using
Query Analyzer (a SQL Server query  tool), test it in that
environment and then just paste the query into the textbox.
3) I use the msgbox approach to edit errors and have already checked
that.

That said, I'm happy to hear you've used SQL variable naming
conventions in a VBA procedure. It gives me hope that this problem
can be corrected.

This is my code  below. I'm using a QueryTable so maybe that is the
problem. What method do you use?

FYI - the error is on the 'oQT.Refresh' and like I've said, if I
just take out the '@'+variable name and replace the variables  with
constants the process works as intended.

Sub CreateQT3()

Dim sConn As String
Dim sSql As String
Dim oQt As QueryTable
Dim intdate As Long
Dim strOut As String
Dim shtOut As String
Dim wkbkOut As String
Dim wkbk As Workbook

Set wkbk = ActiveWorkbook

wkbkOut = Sheet3.Range("AJ27").Value
shtOut = Sheet3.Range("AJ28").Value
strOut = Sheet3.Range("AJ29").Value

sConn = "ODBC;DRIVER=SQL
Server;SERVER=xxxxxxxx;UID=xxxxxxx;Trusted_Connection=Yes"

sSql = Sheets("Queries").AccessQry.Text

Set oQt = Workbooks(wkbkOut).Sheets(shtOut).QueryTables.Add
(Connection:=sConn, _
Destination:=Sheets(shtOut).Range(strOut), Sql:=sSql)
With oQt
.Name = "QueryResults"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 1
.PreserveColumnInfo = False
End With
oQt.Refresh
oQt.Delete
End Sub

 
Answer #3    Answered By: Eric Foster     Answered On: Jan 19

We might be talking at cross-purposes. What exactly does your SQL statement
look like?

For Sybase, the only time I would use @ is when naming a parameter in a
stored procedure call. (It might be possible to define and use local
variables from inside an SQL call, but I've never wanted to try. If it's
that complex, I define it as a stored procedure.)

(Note that I don't use QueryTables, I use direct SQL calls, including stored
procedure calls - and there could well be a significant difference.)

If you are referring to variables created  as a result of some other action,
will those variables  still exist, or be in scope of your SQL statement?

 
Answer #4    Answered By: Oliver Evans     Answered On: Jan 19

Could I pry a short snippet of your Server connection/query
submission/retrieval code  from you? I have zero experience with DAO
or RDO, one of which I'm assuming you're using, so an example  might
come in handy. I'd assume the only programmatic differences between
SQLServer and Sybase are connection strings and driver references.

 




Tagged: