Query with single quotes using OPENROWSET

I'm trying to pass through a SQL statement to an Oracle database using
OPENROWSET. My problem is that I'm not sure of the exact syntax I need
to use when the SQL statement itself contains single quotes.
Unfortunately, OPENROWSET doesn't allow me to use parameters so I can't
get around the problem by assigning the SQL statement to a parameter of
type varchar or nvarchar as in

SELECT *
FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword',
@.chvSQL)

I tried doubling the single quotes as in

SELECT *
FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword',
'SELECT *
FROM A
WHERE DateCol > To_Date(''2002-12-01'', ''yyyy-mm-dd'')'
)

But that didn't work. Is there a way out of this?

Thanks,

Bill E.
Hollywood, FLI take it back--doubling the single quotes is working fine now. I
don't know why I had trouble before.

Bill

Query using datetime datatype

hello,
so i have a table with column name Date with datetime as it's datatype. I'm
trying to run a select statement on it that will give me all rows where my
Date column has a datetime of 30 days or more. Can anyone help? I tried using
datediff but can't get it to work.
Thanks in advance!"FS" <FS@.discussions.microsoft.com> wrote in message
news:6A6874E0-38C5-4257-BA2A-4EEFF4D3A181@.microsoft.com...
> hello,
> so i have a table with column name Date with datetime as it's datatype.
> I'm
> trying to run a select statement on it that will give me all rows where my
> Date column has a datetime of 30 days or more. Can anyone help? I tried
> using
> datediff but can't get it to work.
> Thanks in advance!
WHERE dt <= DATEADD(DAY,-30,CURRENT_TIMESTAMP);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Query Timeout

I have a rather complicated query statement in a stored proc which all of a
sudden hangs in production. I copy the same database to QA and run the store
d
proc and it executes in less than a second. The explain plans are different
between production and QA. I have recompiled (sp_recompile) the stored proc
in production. Still hangs. I have run sp_updatestats in production (but tha
t
still doesn't explain why QA works fine) and it still hangs. Any ideas?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1Double-check the indexes on the production and QA boxes first to make sure
they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
production if the indexes are the same.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:64ebbf94f87e0@.uwe...
>I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the
> stored
> proc and it executes in less than a second. The explain plans are
> different
> between production and QA. I have recompiled (sp_recompile) the stored
> proc
> in production. Still hangs. I have run sp_updatestats in production (but
> that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>|||cbrichards via droptable.com wrote:
> I have a rather complicated query statement in a stored proc which all of
a
> sudden hangs in production. I copy the same database to QA and run the sto
red
> proc and it executes in less than a second. The explain plans are differen
t
> between production and QA. I have recompiled (sp_recompile) the stored pro
c
> in production. Still hangs. I have run sp_updatestats in production (but t
hat
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>
When the query "hangs", check sysprocesses to see what's blocking it...
My guess is your production server has more activity on it than the QA
server, and something is blocking your query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The indexes are the same as I copied the database to QA. I have another post
on this website that Tracy has been responding to about the DBREindex not
having any affect on my fragmented index. But there has not been a response
on that issue since posting my SHOWCONTIG results.
Mike C# wrote:[vbcol=seagreen]
>Double-check the indexes on the production and QA boxes first to make sure
>they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
>production if the indexes are the same.
>
>[quoted text clipped - 6 lines]
Message posted via http://www.droptable.com|||Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
running has not revealed any blocking. Initially I suspected that too. I am
still stumped over the lack of affect of DBReindex on my indexes another pos
t
you responded too.
Tracy McKibben wrote:
>When the query "hangs", check sysprocesses to see what's blocking it...
> My guess is your production server has more activity on it than the QA
>server, and something is blocking your query.
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> The indexes are the same as I copied the database to QA. I have another po
st
> on this website that Tracy has been responding to about the DBREindex not
> having any affect on my fragmented index. But there has not been a respons
e
> on that issue since posting my SHOWCONTIG results.
>
Actually there was a response from another poster, stating that the
index in question is too small to defragment. I didn't respond because
his answer is correct.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||cbrichards via droptable.com wrote:
> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
> running has not revealed any blocking. Initially I suspected that too. I a
m
> still stumped over the lack of affect of DBReindex on my indexes another p
ost
> you responded too.
>
There must be some clue in sysprocesses. Is there a waittype shown for
the query? Does the execution plan reveal any clues?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||When I run "select * from master.dbo.sysprocesses" while the proc is about 3
0
seconds into running (It should complete in less than a half second), I get
the following from sysprocesses:
blocked = 0
lastwaittype = PAGELATCH_SH
CPU = 261204
When I run it in production (and times out) the explain plain provided in
profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
QA does not show in its explain plan.
Tracy McKibben wrote:
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||At about 20 minutes into the proc execution I ran sysprocesses again:
Blocked = 0 (in fact all the rows for the blocked column from sysprocesses
are zero)
LastWaitType = LCK_M_S
Tracy McKibben wrote:
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> When I run "select * from master.dbo.sysprocesses" while the proc is about
30
> seconds into running (It should complete in less than a half second), I ge
t
> the following from sysprocesses:
> blocked = 0
> lastwaittype = PAGELATCH_SH
> CPU = 261204
> When I run it in production (and times out) the explain plain provided in
> profiler shows a Hash join and two Bookmark lookups that the "exact" copy
on
> QA does not show in its explain plan.
>
For the two bookmark lookups, what index is being used? Are they the
same indexes that the query uses in QA? If not, you need to determine
why it's choosing different indexes. Is the amount of data the same
between QA and prod? Try forcing the index using an index hint, see if
that improves the performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Query Timeout

I have a rather complicated query statement in a stored proc which all of a
sudden hangs in production. I copy the same database to QA and run the stored
proc and it executes in less than a second. The explain plans are different
between production and QA. I have recompiled (sp_recompile) the stored proc
in production. Still hangs. I have run sp_updatestats in production (but that
still doesn't explain why QA works fine) and it still hangs. Any ideas?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1Double-check the indexes on the production and QA boxes first to make sure
they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
production if the indexes are the same.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:64ebbf94f87e0@.uwe...
>I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the
> stored
> proc and it executes in less than a second. The explain plans are
> different
> between production and QA. I have recompiled (sp_recompile) the stored
> proc
> in production. Still hangs. I have run sp_updatestats in production (but
> that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||cbrichards via SQLMonster.com wrote:
> I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the stored
> proc and it executes in less than a second. The explain plans are different
> between production and QA. I have recompiled (sp_recompile) the stored proc
> in production. Still hangs. I have run sp_updatestats in production (but that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>
When the query "hangs", check sysprocesses to see what's blocking it...
My guess is your production server has more activity on it than the QA
server, and something is blocking your query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The indexes are the same as I copied the database to QA. I have another post
on this website that Tracy has been responding to about the DBREindex not
having any affect on my fragmented index. But there has not been a response
on that issue since posting my SHOWCONTIG results.
Mike C# wrote:
>Double-check the indexes on the production and QA boxes first to make sure
>they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
>production if the indexes are the same.
>>I have a rather complicated query statement in a stored proc which all of a
>> sudden hangs in production. I copy the same database to QA and run the
>[quoted text clipped - 6 lines]
>> that
>> still doesn't explain why QA works fine) and it still hangs. Any ideas?
--
Message posted via http://www.sqlmonster.com|||Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
running has not revealed any blocking. Initially I suspected that too. I am
still stumped over the lack of affect of DBReindex on my indexes another post
you responded too.
Tracy McKibben wrote:
>> I have a rather complicated query statement in a stored proc which all of a
>> sudden hangs in production. I copy the same database to QA and run the stored
>> proc and it executes in less than a second. The explain plans are different
>> between production and QA. I have recompiled (sp_recompile) the stored proc
>> in production. Still hangs. I have run sp_updatestats in production (but that
>> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>When the query "hangs", check sysprocesses to see what's blocking it...
> My guess is your production server has more activity on it than the QA
>server, and something is blocking your query.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> The indexes are the same as I copied the database to QA. I have another post
> on this website that Tracy has been responding to about the DBREindex not
> having any affect on my fragmented index. But there has not been a response
> on that issue since posting my SHOWCONTIG results.
>
Actually there was a response from another poster, stating that the
index in question is too small to defragment. I didn't respond because
his answer is correct.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||cbrichards via SQLMonster.com wrote:
> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
> running has not revealed any blocking. Initially I suspected that too. I am
> still stumped over the lack of affect of DBReindex on my indexes another post
> you responded too.
>
There must be some clue in sysprocesses. Is there a waittype shown for
the query? Does the execution plan reveal any clues?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||When I run "select * from master.dbo.sysprocesses" while the proc is about 30
seconds into running (It should complete in less than a half second), I get
the following from sysprocesses:
blocked = 0
lastwaittype = PAGELATCH_SH
CPU = 261204
When I run it in production (and times out) the explain plain provided in
profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
QA does not show in its explain plan.
Tracy McKibben wrote:
>> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
>> running has not revealed any blocking. Initially I suspected that too. I am
>> still stumped over the lack of affect of DBReindex on my indexes another post
>> you responded too.
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||At about 20 minutes into the proc execution I ran sysprocesses again:
Blocked = 0 (in fact all the rows for the blocked column from sysprocesses
are zero)
LastWaitType = LCK_M_S
Tracy McKibben wrote:
>> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
>> running has not revealed any blocking. Initially I suspected that too. I am
>> still stumped over the lack of affect of DBReindex on my indexes another post
>> you responded too.
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> When I run "select * from master.dbo.sysprocesses" while the proc is about 30
> seconds into running (It should complete in less than a half second), I get
> the following from sysprocesses:
> blocked = 0
> lastwaittype = PAGELATCH_SH
> CPU = 261204
> When I run it in production (and times out) the explain plain provided in
> profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
> QA does not show in its explain plan.
>
For the two bookmark lookups, what index is being used? Are they the
same indexes that the query uses in QA? If not, you need to determine
why it's choosing different indexes. Is the amount of data the same
between QA and prod? Try forcing the index using an index hint, see if
that improves the performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Query Timeout

Hi there,
I have a java application which connects to an sqlServer database and issues
an SQL update statement.
When you attempt to connect to the database if there is no connection then
this is fine, it will retry for x times and timeout at the time you have
set.
The problem is if you have made a valid connection and then disconnect the
database from the network and try to run the sql UPDATE. The update will
only attempt one time and basically hang until the connection is
re-established.
The other way round, if the machine the java application is running on is
disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
times and times out at the interval set.
It only seems to hang if the java application is still connected but the
database is disconnected. Is this a general problem with the jdbc driver?
Regards
Jamie
Jamie wrote:

> Hi there,
> I have a java application which connects to an sqlServer database and issues
> an SQL update statement.
> When you attempt to connect to the database if there is no connection then
> this is fine, it will retry for x times and timeout at the time you have
> set.
> The problem is if you have made a valid connection and then disconnect the
> database from the network and try to run the sql UPDATE. The update will
> only attempt one time and basically hang until the connection is
> re-established.
> The other way round, if the machine the java application is running on is
> disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
> times and times out at the interval set.
> It only seems to hang if the java application is still connected but the
> database is disconnected. Is this a general problem with the jdbc driver?
> Regards
> Jamie
It's at a lower level than that. The failure you cause means the TCP stack will
take minutes before it notifies the driver that the socket is dead. You might
try setting the query timeout on your statement before executing it. Then the
driver may be able to return control to you sooner.
Joe Weinstein at BEA

Query text column.

Hi,
When I using the Query Analyze do the select statement from table, the
column set up with data type text always truncate at right side. Any method
gets complete data from that text data type column?
Regards!
ChenIn SQL Query Analyzer, go to:
Tools/Options/Results Tab and set the Maximum characters per column to the
desired value E.g. 8000
Let me know if it works for you...
"Chen" wrote:
> Hi,
> When I using the Query Analyze do the select statement from table, the
> column set up with data type text always truncate at right side. Any method
> gets complete data from that text data type column?
> Regards!
> Chen
>|||Yes, it's working. Thanks a lot.
"Edgardo Valdez, MCSD, MCDBA" wrote:
> In SQL Query Analyzer, go to:
> Tools/Options/Results Tab and set the Maximum characters per column to the
> desired value E.g. 8000
> Let me know if it works for you...
> "Chen" wrote:
> > Hi,
> >
> > When I using the Query Analyze do the select statement from table, the
> > column set up with data type text always truncate at right side. Any method
> > gets complete data from that text data type column?
> >
> > Regards!
> > Chen
> >|||You are welcome!
Glad to be able to answer the question.
"Chen" wrote:
> Yes, it's working. Thanks a lot.
> "Edgardo Valdez, MCSD, MCDBA" wrote:
> > In SQL Query Analyzer, go to:
> >
> > Tools/Options/Results Tab and set the Maximum characters per column to the
> > desired value E.g. 8000
> >
> > Let me know if it works for you...
> >
> > "Chen" wrote:
> >
> > > Hi,
> > >
> > > When I using the Query Analyze do the select statement from table, the
> > > column set up with data type text always truncate at right side. Any method
> > > gets complete data from that text data type column?
> > >
> > > Regards!
> > > Chen
> > >

Query text column.

Hi,
When I using the Query Analyze do the select statement from table, the
column set up with data type text always truncate at right side. Any method
gets complete data from that text data type column?
Regards!
ChenIn SQL Query Analyzer, go to:
Tools/Options/Results Tab and set the Maximum characters per column to the
desired value E.g. 8000
Let me know if it works for you...
"Chen" wrote:

> Hi,
> When I using the Query Analyze do the select statement from table, the
> column set up with data type text always truncate at right side. Any metho
d
> gets complete data from that text data type column?
> Regards!
> Chen
>|||Yes, it's working. Thanks a lot.
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> In SQL Query Analyzer, go to:
> Tools/Options/Results Tab and set the Maximum characters per column to the
> desired value E.g. 8000
> Let me know if it works for you...
> "Chen" wrote:
>|||You are welcome!
Glad to be able to answer the question.
"Chen" wrote:
[vbcol=seagreen]
> Yes, it's working. Thanks a lot.
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>

Query text column.

Hi,
When I using the Query Analyze do the select statement from table, the
column set up with data type text always truncate at right side. Any method
gets complete data from that text data type column?
Regards!
Chen
In SQL Query Analyzer, go to:
Tools/Options/Results Tab and set the Maximum characters per column to the
desired value E.g. 8000
Let me know if it works for you...
"Chen" wrote:

> Hi,
> When I using the Query Analyze do the select statement from table, the
> column set up with data type text always truncate at right side. Any method
> gets complete data from that text data type column?
> Regards!
> Chen
>
|||Yes, it's working. Thanks a lot.
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> In SQL Query Analyzer, go to:
> Tools/Options/Results Tab and set the Maximum characters per column to the
> desired value E.g. 8000
> Let me know if it works for you...
> "Chen" wrote:
|||You are welcome!
Glad to be able to answer the question.
"Chen" wrote:
[vbcol=seagreen]
> Yes, it's working. Thanks a lot.
> "Edgardo Valdez, MCSD, MCDBA" wrote: