February 10, 2008 at 2:10 am
I have a stroed procedure which access remote data through linked server. Following is the statement in the SP
[font="Courier New"]exec('select a.invoiceno,a.invoicedate,convert(varchar,a.invoicedate,113) as strInvDate,totalvalue
from openquery('+ @SERVERNAME +',
''SELECT top 1 pos_id,invoiceno,invoicedate,totalvalue From TestPOS.dbo.sal_invoices where doctype=2080
order by invoicedate desc,invoiceno desc'') a, MAX_INVOICES b
where b.terminalid='+ @TERMINAL + ' AND a.pos_id=b.terminalid and
a.invoicedate>b.Invoicedate')[/font]
the SP is working fine when executed from SQL query analyzer
But when I am trying this from VB6, it returns an error as below:
[font="Courier New"][OLE/DB provider returned message: Invalid authorization specification][/font]
Can anybody help please...
thanks in advance.
Thomas George
February 10, 2008 at 6:41 am
Chances are you are not using the same user account in query analyser as you're using in your VB6 application. Log into query analyser using the application's user account and then see if it works or if you get the same error.
Also, try to not use OpenQuery if at all possible 🙂 As you can see it means you have awful nested quoting of strings. If it cannot be avoided then use the QUOTENAME system function or write your own UDF to put quotes around a string to avoid looking at things like ' ''' ''' 🙂 You'll also be less susceptible to SQL injection attacks if you use 4 part naming to access the linked server instead as in
select * from [otherServer].[otherDatabase].[otherSchema].[otherTable] where otherSchema is usually DBO and otherServer is the name of the linked server you've already configured.
February 11, 2008 at 11:00 pm
Yes exactly. I was not using the same user account.
Now its working with the same account.
Thanks a lot Ian..:)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply