• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

TSQL Query

I have a long query with the in Excel VBA

Rs.Open stSQL, conn, adOpenKeyset, adLockReadOnly

The stSQL contains two parts
1. select [some fields] from TableA into #TMP1
2. select [some fields from #TMP1 INNER JOIN TableB on ....

The first part retrieved a temporary solution into a temp table and the second part select fields based on joining the temp table to another table.

If I paste the whole SQL and run on SQL  Management Studio. It works fine. However, no recordset can be retrieved through the VBA. Can VBA SQL allows directing 1st part of the result to a temporary table in order to subsequent query to use. Is there any workaround way to make it work ?

Tks
0
AXISHK
Asked:
AXISHK
  • 3
  • 3
  • 2
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
How about using a Stored Procedure?
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Are you changing connections in between? The single hash (#) indicates a local temporary table that is not accessible across connections.

It's similar to the following:
1. Open SSMS and connect 2 query editor windows to a SQL server instance
2. Paste your 1st step (SELECT .... INTO #temp1) in the 1st window
3. Paste the 2nd step (SELECT ... FROM #temp1) in the 2nd window
4. Run the query in window #1 followed by a query in window #2 - it will be unable to find the object - #temp1
5. Now, switch back to the 1st window and run (SELECT .... FROM #temp1) - it will succeed and return results

The workaround that immeidately comes to mind is to have a permanent transaction table that that the SPID of the process as the key. So, query #1 inserts the result sets with SPID as the key. Query #2 simply queries all records matching that SPID.

Or, use a stored procedure instead (much better from a performance perspective). You can also evaluate using in-line table valued functions.
0
 
AXISHKAuthor Commented:
Tks.  Any example of calling the procedure on the SQL server from VBA, or create a stored procedure in VBA and use it ?


Great Tks.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Here's how to do it in Excel (i.e. not in VBA): http://blogs.office.com/2010/06/07/running-a-sql-stored-procedure-from-excel-no-vba/

Here's an example of how to do in Excel VBA: http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28388090.html

There are other examples, but I'm not allowed to post them. Google

call a sql server stored procedure from vba

for more examples.
0
 
AXISHKAuthor Commented:
With sql stored procedure, what is max. size of a single input parameter ? For @InvoiceNo nvarchar(3000), does it mean it only allow 3000 chars to pass ?

Tks
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
You are correct. NVARCHAR(3000) means that it will accept 3000 Unicode characters. Only exceptions are the datatypes like NVARCHAR(MAX) or VARCHAR(MAX) that support upto 2 GB of data.
0
 
AXISHKAuthor Commented:
Tks. Will there be any issue if I define my input parameter as NVARCHAR(MAX) to ensure it can handle the maximum size input ?
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Not too many issues. Can't be indexed, but that isn't relevant to you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now