How to create paramaters for a query that cannot be represented graphically?

How to create paramaters for a query that cannot be represented graphically?  I have 2 querys that run super fast in SQL.  I want to use these in excel(MS QUery), when combine these two queries using Union All the query is not longer represented graphically and will not allow me to use a Parameter =?
Is there a work around for this?
kgittingerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
You can't use MSQuery for that - you will have to use ADO and pass the parameters to the procedure that way. We'd need more details of the SP and the parameters to be more specific.
0
kgittingerAuthor Commented:
I can copy my SQL if that will help
Do you have time to help trouble shoot?
0
stevericeCommented:
What version of Excel are you using? You can paste your SQL directly into the data properties in Excel 2007. If you're using 2007 let me know and I'll walk you through it.  On earlier versions of Excel, I have overcome this problem in the past by creating a view based on the SQL select and then using MS Query to query that view.

Steve
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

kgittingerAuthor Commented:
I am using Excel 2007
Thanks so much.
0
stevericeCommented:
It's a bit of a work-round, but it works.....

In Excel 2007, Go to the Data tab on the toolbar.

Click on the "From Other Data Sources" tool in the "Get External Data" ribbon.

Select "Microsoft Query" (I'm sure you've done this much before...)

Select your data source in the "Chose Data Source" and press OK.

This opens MS Query.

Close the "Add Tables Dialog" - this leaves you with a blank MS Query Screen.

Click "View" on the menubar and select 'SQL'

Paste your sql in the resulting window.

Replace any variable parameters you've got with hard-coded criteria (don't worry, we'll replace these later).

Click OK.

You'll get a dialog to say that the query cannot be displayed graphically. Click OK.

You should see your results set in the query window.

Click "file" on the menubar and return the data to Excel.

Excel will prompt you for where you want the data inserted. Select a cell and press OK.

Your data (with the hard-coded parameters) should now be in Excel.

On the Data tab, click on "Connections". This will list all of the data connections you have in the spreadsheet - presumably only one will be on the list for this example.

Select the connection by clicking on its name and press the "Properties" button.

Click on the "definition" tab. Your SQL is now displayed In the "Command Text" window. You can now edit this to replace your hard-coded selection criteria with the variable "?" and the query should run as expected.

Did you know you could also point this variable to a cell in your spreadsheet?

I hope this helps.

Regards
Steve

 

 



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kgittingerAuthor Commented:
Unfortunately I receive [Microsoft][ODBC SQL Server Driver] Invalid paramter number
 and
[Microsoft][ODBC SQL Server Driver] Invalid Descriptor Index.

Yes, it this all works, I want to have the parmeter typed into my spreadsheet.

0
stevericeCommented:
How far did you get - i.e. when did you receive the error message(s)
Steve
0
kgittingerAuthor Commented:
I added the ? in for my two parameters and hit OK  Thank you so much for helping me with this.  It is so close...
0
kgittingerAuthor Commented:
Here is my SQL  I have entered employer_code = ? in two places...
SELECT Table_Payments_And_Repayments_Ver2.employer_code, 
Table_Payments_And_Repayments_Ver2.employer_name, 
Table_Payments_And_Repayments_Ver2.planyear_end_dt, 
Table_Payments_And_Repayments_Ver2.plan_name, 
Table_Payments_And_Repayments_Ver2.plan_display_name, 
Table_Payments_And_Repayments_Ver2.participant_first_name, 
Table_Payments_And_Repayments_Ver2.participant_last_name, 
Table_Payments_And_Repayments_Ver2.participant_division_lookup, 
Table_Payments_And_Repayments_Ver2.claimreimbursement_dt AS 'Transaction _Date', 
Table_Payments_And_Repayments_Ver2.claimreimbursement_paid_amt AS 'Transaction_Amount', 
Table_Payments_And_Repayments_Ver2.paymentdetail_actualmethod_type_lookup AS 'Transaction_Type', 
Table_Payments_And_Repayments_Ver2.claimreimbursement_claim_nbr AS 'Claim_Number'
FROM TPA4.dbo.Table_Payments_And_Repayments_Ver2 Table_Payments_And_Repayments_Ver2
where (Table_Payments_And_Repayments_Ver2.employer_code = ?) and 
	Datepart(yy,Table_Payments_And_Repayments_Ver2.planyear_end_dt) = 2008


Union all
SELECT Table_Debitcard_Transactions_Ver2.employer_code, 
Table_Debitcard_Transactions_Ver2.employer_name, 
Table_Debitcard_Transactions_Ver2.planyear_end_dt, 
Table_Debitcard_Transactions_Ver2.plan_name, 
Table_Debitcard_Transactions_Ver2.plan_display_name, 
Table_Debitcard_Transactions_Ver2.participant_first_name, 
Table_Debitcard_Transactions_Ver2.participant_last_name, 
Table_Debitcard_Transactions_Ver2.participant_division_lookup, 
Table_Debitcard_Transactions_Ver2.debitcardtransaction_settlement_dt AS 'Transaction_Date', 
CASE
  when debitcardtransaction_type_lookup  = 'Refund' 
then -1 * debitcardtransaction_transaction_amt
  else debitcardtransaction_transaction_amt
END Transaction_Amount, 
Table_Debitcard_Transactions_Ver2.debitcardtransaction_type_lookup AS 'Transaction Type', 
Table_Debitcard_Transactions_Ver2.claim_claim_nbr AS 'Claim_Number'
FROM TPA4.dbo.Table_Debitcard_Transactions_Ver2 Table_Debitcard_Transactions_Ver2
WHERE (Table_Debitcard_Transactions_Ver2.employer_code = ?) 
and datepart(yy,Table_Debitcard_Transactions_Ver2.planyear_end_dt) = 2008 
and (Table_Debitcard_Transactions_Ver2.debitcardtransaction_type_lookup =' SettledTransaction' 
Or Table_Debitcard_Transactions_Ver2.debitcardtransaction_type_lookup='Refund' 
or Table_Debitcard_Transactions_Ver2.debitcardtransaction_type_lookup='Forecpost'
or Table_Debitcard_Transactions_Ver2.debitcardtransaction_type_lookup='SettledTransaction')

Open in new window

0
kgittingerAuthor Commented:
I was able to get this to work for the first occurance, but not the second (in the union all select)  WHen I add just the ? to the (Table_Debitcard_Transactions_Ver2.employer_code = ?)  in teh bottom portion , I receive unable to derive parameter information when parameter marker is a funciotn argument.  Do you have a suggestion for overcoming this?

Thanks a million!
0
stevericeCommented:
Hi,

Take a look at http://support.microsoft.com/kb/821789. This refers to an earlier problem with Excel and provides a registry tweak to fix it.

It may be worth a try.

I'm sure I don't have to tell you to be very careful when editing the registry......

Steve
0
kgittingerAuthor Commented:
This is what I asked for although I was unsuccessful in implementing with a union query

Thanks for the information!
0
stevericeCommented:
Thanks for the points but I don't think you should give up yet.

You could use your union query (with no parameters) to create a view of your data. You could then query this view from Excel - with the ability to pass it the parameter (as I think if I've read your SQL correctly - there will now only be one on 'employer_code') as described above. I think it's worth a try....
0
kgittingerAuthor Commented:
Great. I will try to create the view!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.