Online Data Transform: queries

Showing posts with label queries. Show all posts

Friday, March 30, 2012

OpenQuery with join

Hi

I have a SP which queries a linked server using OpenQuery function.
The remote query includes a join and an IN clause to get the desire result. ( The linked server uses Transoft ODBC driver.)

The qry looks something like this:

select * from OpenQuery(SERVER1,
'select
distinct C.item, C.operation, C.STD_OPERATION,
D.operation_desc , C.operation_desc operation_desc
from TABLE1 C left join
(select distinct operation, operation_desc from TABLE1
where operation in (select distinct STD_OPERATION from TABLE1 where
item = ''9999999'' AND STD_OPERATION <> 0)
AND item = ''STANDARD'') D
on D.operation = C.STD_OPERATION where C.item = ''9999999'' ')

When I run this Qry I get the following error:

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Transoft][TSODBC][usqlsd]')' expected here (DISTINCT)]

Any help would be greatly appreciated.
thxIs the problem that Transoft can't handle the distinct keyword? It is SQL-92 compliant, but maybe the driver can't handle it? Have you tried removing distinct and running the query again?

If this is the problem, you should be able to work around the problem using a group by clause.

Hth.

Paul Barbin

OPENQUERY and Windows Authentication

We have a SQL Server and an Oracle Instance. I have been using OPENQUERY to run queries on SQL Server to compare datasets on SQL Server with those in Oracle. Yesterday, I switched from using a SQL Server login to using Windows Authentication.
My OPENQUERY returned Msg 7399, "Access Denied". So, I added a new linked server login for my windows username:
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'ORAINST',
@.useself = false,
@.locallogin = 'NTDOMAIN\NTGROUP',
@.rmtuser = 'ORAUSER',
@.rmtpassword = 'ORAPWD'
I continued getting the same error, after monkeying with the case of my @.loginlogin info and switching my NTUSER for NTGROUP; I finally opened up the login to all users on the server:
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'ORAINST',
@.useself = false,
@.locallogin = NULL,
@.rmtuser = 'ORAUSER',
@.rmtpassword = 'ORAPWD'
I still get the same error. The examples in BOL suggest that setting up a linked server login for a windows user should be the same as for a SQL user. Any ideas what I should try next?You cannot create a mapping for a group...only a user.

But you seem to suggest that even that is not working.
Can you provide the complete error message. 7399 usually is followed by an error from the provider.

This might be a bit obvious but make sure the local login that is being used on the server is infact setup with the login mapping. sys.linked_logins view should give the current settings. Also make sure the oracle user name and password are correctly setup in the sytem catalogs.

Wednesday, March 28, 2012

Opening Solution clears previous queries in SSMS

Yikes!
Does anyone know of a way that I can open a solution but keep my current
query windows open? Every time I go to File -> Open -> Project/Solution, it
kills any query windows I was working in.
And while I'm here, is there a way to set SSMS to open with Solution the way
you can have it open a query window when you connect?
Help is appreciated. Thanks,
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)Catadmin wrote:
> Yikes!
> Does anyone know of a way that I can open a solution but keep my current
> query windows open? Every time I go to File -> Open -> Project/Solution, it
> kills any query windows I was working in.
> And while I'm here, is there a way to set SSMS to open with Solution the way
> you can have it open a query window when you connect?
> Help is appreciated. Thanks,
> Catadmin
Launch another instance of SSMS?
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yeah, but I'd kinda like to open up the solution in my current instance of
SSMS.
I have a solution where I keep my commonly used code templates to fix data.
During the course of a day, I might be working on new development when
suddenly I get help desk tickets asking for data fixes because someone broke
something. Rather than lose what I've got open, or have to save & reopen it,
or have to go to the whole Start->Programs hoo-ha (which is a pain when a
running query keeps pulls the focus back to SSMS while I'm trying to get to
something on the Start->Programs path), I'd like to just be able to open my
solution in Solution Explorer and have everything else remain the same.
Plus, if I open up another instance, I have to do cut-n-paste from one
instance to another. Bleargh. There's got to be a better way.
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Tracy McKibben" wrote:
> Catadmin wrote:
> > Yikes!
> >
> > Does anyone know of a way that I can open a solution but keep my current
> > query windows open? Every time I go to File -> Open -> Project/Solution, it
> > kills any query windows I was working in.
> >
> > And while I'm here, is there a way to set SSMS to open with Solution the way
> > you can have it open a query window when you connect?
> >
> > Help is appreciated. Thanks,
> >
> > Catadmin
> Launch another instance of SSMS?
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Another option is to just keep those scripts in a particular
directory. Then you can create a menu item in SSMS that will
open that particular directory. That's about what I've done
at some places for the same reasons - to get quick access to
fire fighting scripts and not lose whatever else I have
open. Just go to Tools, External Tools and create a new
entry - Command Explorer and Argument of the script
directory. That might work for you.
-Sue
On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:
>Yeah, but I'd kinda like to open up the solution in my current instance of
>SSMS.
>I have a solution where I keep my commonly used code templates to fix data.
> During the course of a day, I might be working on new development when
>suddenly I get help desk tickets asking for data fixes because someone broke
>something. Rather than lose what I've got open, or have to save & reopen it,
>or have to go to the whole Start->Programs hoo-ha (which is a pain when a
>running query keeps pulls the focus back to SSMS while I'm trying to get to
>something on the Start->Programs path), I'd like to just be able to open my
>solution in Solution Explorer and have everything else remain the same.
>Plus, if I open up another instance, I have to do cut-n-paste from one
>instance to another. Bleargh. There's got to be a better way.|||Sue,
I see what you're talking about with the Tools -> External Tools, but I'm
not clear on what you mean by Command Explorer. Is there a particular .exe
that you're refering to with this?
Thanks,
Catadmin
"Sue Hoegemeier" wrote:
> Another option is to just keep those scripts in a particular
> directory. Then you can create a menu item in SSMS that will
> open that particular directory. That's about what I've done
> at some places for the same reasons - to get quick access to
> fire fighting scripts and not lose whatever else I have
> open. Just go to Tools, External Tools and create a new
> entry - Command Explorer and Argument of the script
> directory. That might work for you.
> -Sue
> On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
> <goldpetalgraphics@.yahoo.com> wrote:
> >Yeah, but I'd kinda like to open up the solution in my current instance of
> >SSMS.
> >
> >I have a solution where I keep my commonly used code templates to fix data.
> > During the course of a day, I might be working on new development when
> >suddenly I get help desk tickets asking for data fixes because someone broke
> >something. Rather than lose what I've got open, or have to save & reopen it,
> >or have to go to the whole Start->Programs hoo-ha (which is a pain when a
> >running query keeps pulls the focus back to SSMS while I'm trying to get to
> >something on the Start->Programs path), I'd like to just be able to open my
> >solution in Solution Explorer and have everything else remain the same.
> >
> >Plus, if I open up another instance, I have to do cut-n-paste from one
> >instance to another. Bleargh. There's got to be a better way.
>|||Sorry about that - it's not real clear from what I typed.
The exe is Explorer. For the Command, you reference:
%SystemRoot%\explorer.exe
so that it fires off Windows Explorer. It will start in
whatever directory you list for the Arguments.
-Sue
On Mon, 11 Sep 2006 04:36:02 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:
>Sue,
>I see what you're talking about with the Tools -> External Tools, but I'm
>not clear on what you mean by Command Explorer. Is there a particular .exe
>that you're refering to with this?
>Thanks,
>Catadmin
>"Sue Hoegemeier" wrote:
>> Another option is to just keep those scripts in a particular
>> directory. Then you can create a menu item in SSMS that will
>> open that particular directory. That's about what I've done
>> at some places for the same reasons - to get quick access to
>> fire fighting scripts and not lose whatever else I have
>> open. Just go to Tools, External Tools and create a new
>> entry - Command Explorer and Argument of the script
>> directory. That might work for you.
>> -Sue
>> On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
>> <goldpetalgraphics@.yahoo.com> wrote:
>> >Yeah, but I'd kinda like to open up the solution in my current instance of
>> >SSMS.
>> >
>> >I have a solution where I keep my commonly used code templates to fix data.
>> > During the course of a day, I might be working on new development when
>> >suddenly I get help desk tickets asking for data fixes because someone broke
>> >something. Rather than lose what I've got open, or have to save & reopen it,
>> >or have to go to the whole Start->Programs hoo-ha (which is a pain when a
>> >running query keeps pulls the focus back to SSMS while I'm trying to get to
>> >something on the Start->Programs path), I'd like to just be able to open my
>> >solution in Solution Explorer and have everything else remain the same.
>> >
>> >Plus, if I open up another instance, I have to do cut-n-paste from one
>> >instance to another. Bleargh. There's got to be a better way.
>>|||Thank you, Sue, but that's still not quite what I want. This doesn't enable
me to open an entire solution in SSMS while keeping current query windows
open.
If anyone from MS is reading this post, I'm sure there are other people who
would like to do the same thing I want to do (hint, hint).
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Sue Hoegemeier" wrote:
> Sorry about that - it's not real clear from what I typed.
> The exe is Explorer. For the Command, you reference:
> %SystemRoot%\explorer.exe
> so that it fires off Windows Explorer. It will start in
> whatever directory you list for the Arguments.
> -Sue
> On Mon, 11 Sep 2006 04:36:02 -0700, Catadmin
> <goldpetalgraphics@.yahoo.com> wrote:
> >Sue,
> >
> >I see what you're talking about with the Tools -> External Tools, but I'm
> >not clear on what you mean by Command Explorer. Is there a particular .exe
> >that you're refering to with this?
> >
> >Thanks,
> >
> >Catadmin
> >
> >"Sue Hoegemeier" wrote:
> >
> >> Another option is to just keep those scripts in a particular
> >> directory. Then you can create a menu item in SSMS that will
> >> open that particular directory. That's about what I've done
> >> at some places for the same reasons - to get quick access to
> >> fire fighting scripts and not lose whatever else I have
> >> open. Just go to Tools, External Tools and create a new
> >> entry - Command Explorer and Argument of the script
> >> directory. That might work for you.
> >>
> >> -Sue
> >>
> >> On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
> >> <goldpetalgraphics@.yahoo.com> wrote:
> >>
> >> >Yeah, but I'd kinda like to open up the solution in my current instance of
> >> >SSMS.
> >> >
> >> >I have a solution where I keep my commonly used code templates to fix data.
> >> > During the course of a day, I might be working on new development when
> >> >suddenly I get help desk tickets asking for data fixes because someone broke
> >> >something. Rather than lose what I've got open, or have to save & reopen it,
> >> >or have to go to the whole Start->Programs hoo-ha (which is a pain when a
> >> >running query keeps pulls the focus back to SSMS while I'm trying to get to
> >> >something on the Start->Programs path), I'd like to just be able to open my
> >> >solution in Solution Explorer and have everything else remain the same.
> >> >
> >> >Plus, if I open up another instance, I have to do cut-n-paste from one
> >> >instance to another. Bleargh. There's got to be a better way.
> >>
> >>
>|||No it won't open a solution. Like I said it will allow you
to open scripts in a specific directory but not a solution.
I think it's the only way around not losing what queries you
already have open if you don't want to open another SSMS
session. You could always search the product feedback site
to see if others posted this or post the request yourself:
http://lab.msdn.microsoft.com/productfeedback/
-Sue
On Tue, 19 Sep 2006 03:51:01 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:
>Thank you, Sue, but that's still not quite what I want. This doesn't enable
>me to open an entire solution in SSMS while keeping current query windows
>open.
>If anyone from MS is reading this post, I'm sure there are other people who
>would like to do the same thing I want to do (hint, hint).
>Catadmin

Monday, February 20, 2012

online sites

What are the good online sites for T-SQL Queries, Joins and stored
procedures?Hi
For the syntax of your statements and some examples see Books Online or the
online equivalent at
http://msdn.microsoft.com/library/d... />
n_6lyk.asp
for learning T-SQL you may want to look at subscribing to SQL Server
Magazine http://www.windowsitpro.com/SQLServer/ and SQL Server professional
http://www.pinpub.com/ME2/Audiences/Default.asp Many other sites are listed
on http://www.aspfaq.com/show.asp?id=2423
John
"intermediate" <enlight.v@.gmail.com> wrote in message
news:1136150275.405829.321770@.z14g2000cwz.googlegroups.com...
> What are the good online sites for T-SQL Queries, Joins and stored
> procedures?
>

Friday, March 30, 2012

OpenQuery with join

OPENQUERY and Windows Authentication

Wednesday, March 28, 2012

Opening Solution clears previous queries in SSMS

Opening Solution clears previous queries in SSMS

Monday, February 20, 2012

online sites

Online Data Transform

Blog Archive

About Me