OPENQUERY Question. How to update using 2 tables

All,

Can someone help me with the following SQL and help me write it in an
OPENQUERY format. I am running the following code from a SQL Server 7
box, trying to update a table in an Oracle Linked Server. The code
runs fine, except it takes almost an hour to complete. I know if I run
via OPENQUERY,I can get the same done in much less time.

Some of the relevant information is as follows:

ORACLE_HBCPRD04 is a linked Oracle Server.
SITEADDRESS is a table in Oracle
#SiteAddress_New is a table in SQL Server.

UPDATE ORACLE_HBCPRD04...SITEADDRESS
SET
CUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR1 END,
CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR2 END ,
CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR3 END,
CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR4 END ,
CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSE
SiteAddress_New.CTY_NM END,
ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSE
SiteAddress_New.ST_ABBR END,
POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSE
SiteAddress_New.POST_CD END,
CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTY_NM END,
CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTRY_NM END,
ADDR_STAT = NULL ,
LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATE

FROM
ORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN
#SiteAddress_New SiteAddress_New ON
SiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_ID

WHERE
UPPER(SiteAddress_New.PROCESS_CODE) = 'U'

Best Regards,

addi"addi" <addi_s@.hotmail.com> wrote in message
news:6f426fb3.0406090845.6afdbf32@.posting.google.c om...
> All,
> Can someone help me with the following SQL and help me write it in an
> OPENQUERY format. I am running the following code from a SQL Server 7
> box, trying to update a table in an Oracle Linked Server. The code
> runs fine, except it takes almost an hour to complete. I know if I run
> via OPENQUERY,I can get the same done in much less time.
> Some of the relevant information is as follows:
> ORACLE_HBCPRD04 is a linked Oracle Server.
> SITEADDRESS is a table in Oracle
> #SiteAddress_New is a table in SQL Server.
> UPDATE ORACLE_HBCPRD04...SITEADDRESS
> SET
> CUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR1 END,
> CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR2 END ,
> CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR3 END,
> CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR4 END ,
> CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSE
> SiteAddress_New.CTY_NM END,
> ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSE
> SiteAddress_New.ST_ABBR END,
> POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSE
> SiteAddress_New.POST_CD END,
> CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSE
> SiteAddress_New.CNTY_NM END,
> CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSE
> SiteAddress_New.CNTRY_NM END,
> ADDR_STAT = NULL ,
> LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATE
> FROM
> ORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN
> #SiteAddress_New SiteAddress_New ON
> SiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_ID
> WHERE
> UPPER(SiteAddress_New.PROCESS_CODE) = 'U'
> Best Regards,
> addi

I don't really understand your question - OPENQUERY() executes entirely on
the linked server, so it would not be possible to join to a local MSSQL
table.

It's hard to say what the issue is without knowing more about how many rows
are involved, but you might want to look at the REMOTE join hint, to see if
a remote join is faster. This may help if the MSSQL table is much smaller
than the Oracle one.

See also this post:

http://groups.google.com/groups?hl=...1.microsoft.com

Simon|||addi (addi_s@.hotmail.com) writes:
> Can someone help me with the following SQL and help me write it in an
> OPENQUERY format. I am running the following code from a SQL Server 7
> box, trying to update a table in an Oracle Linked Server. The code
> runs fine, except it takes almost an hour to complete. I know if I run
> via OPENQUERY,I can get the same done in much less time.

What you could do is to first insert the data in the temptable into a
table on the Oracle side. Then data in the target table does not have
to move forth and back across the network.

Then you would run the UPDATE statement in Oracle (with Oracle syntax).
The best method would be to so through a stored procedure, but I don't
know exactly what Oracle offers in this area.

OPERQUERY? It may work, but OPENQUERY is not intended for update statements,
but is a rowset provider. You could try:

SELECT 1 FROM OPENQUERY(ORACLE_HBCPRD04, 'UPDATE SITEADDRESS ...')

But if you try the same operation against SQL Server, this will fail
with the messages "...indicates that ... does not return any records".
And I would guess something similar will happen with Oracle. For SQL
Server I know of a poor workaround, but if there is something similar
for Oracle I don't know. Of course you could throw in a dummy SELECT
into the batch you pass to Oracle. And in any case, you will produce
a result set, which might be what calling program might expect. (You
could hide with INSERT EXEC though.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

OPENQUERY and parameters

I am running OPENQUERY against Oracle database via a linked server.
How can I provide parameters into the Select statement?
ThanksWhen I have created openquery statements I create the appropriate SQL on the
fly (which is a pain when you get to single quotes).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:e9ae05NPIHA.4808@.TK2MSFTNGP05.phx.gbl...
>I am running OPENQUERY against Oracle database via a linked server.
> How can I provide parameters into the Select statement?
> Thanks
>|||On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> I am running OPENQUERY against Oracle database via a linked server.
> How can I provide parameters into the Select statement?
> Thanks
What you basically need to do is treat your query like you are writing
a String that contains your query, then EXEC the string at the end.
Any embeded quotation marks need to be "doubled", then you concatenate
your parameters using string concat symbols ( the + sign in SQL
Server ), then EXEC it at the end and you're golden.
DECLARE @.SQLSTR NVARCHAR(4000)
SET @.SQLSTR ='
SELECT * FROM OPENQUERY( PISERVER,
''SELECT TAG, TIME, VALUE
FROM piarchive.piavg
WHERE TAG = '' + @.tagname + ''
AND TIMESTEP = '' + @.timestep + ''
AND TIME >= '' + @.startdatetime + ''
AND TIME <= '' + @.enddatetime + '' '' ) Q
'
EXEC (@.SQLSTR)
-- Scott|||Thanks to you both I got it working:
DECLARE @.SQLSTR NVARCHAR(4000)
SET @.SQLSTR ='
SELECT * FROM OPENQUERY(PST,
''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
work_asgn_id,
descr, trunc(job_work_date) as job_work_date
from EWM.ASSIGNMENT_HISTORY a
inner join EWM.TERMINAL t
on a.work_terminal = t.terminal
where trunc(job_work_date) = to_date( '' + @.ForDate + '',
''''MM/DD/YYYY'''')' +
' and act_offduty_date_time is not null
and act_onduty_date_time is not null
and to_char(a.ASSIGNMENT_HISTORY_SID) in
(select min(to_char(ASSIGNMENT_HISTORY_SID)) from EWM.ASSIGNMENT_HISTORY b
where a.work_asgn_id = b.WORK_ASGN_ID
and a.WORK_TERMINAL = b.WORK_TERMINAL
and trunc(job_work_date) = to_date( '' + @.ForDate + '',
''''MM/DD/YYYY''''))''' +
')'
EXEC (@.SQLSTR)
I run it fine in Data tab, but in Layout when I try to assign an expression
to a field selecting dataset it says:
'DailySummary' dataset has no fields.
What's wrong?
"Orne" <polysillycon@.yahoo.com> wrote in message
news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am running OPENQUERY against Oracle database via a linked server.
>> How can I provide parameters into the Select statement?
>> Thanks
> What you basically need to do is treat your query like you are writing
> a String that contains your query, then EXEC the string at the end.
> Any embeded quotation marks need to be "doubled", then you concatenate
> your parameters using string concat symbols ( the + sign in SQL
> Server ), then EXEC it at the end and you're golden.
>
> DECLARE @.SQLSTR NVARCHAR(4000)
> SET @.SQLSTR => '
> SELECT * FROM OPENQUERY( PISERVER,
> ''SELECT TAG, TIME, VALUE
> FROM piarchive.piavg
> WHERE TAG = '' + @.tagname + ''
> AND TIMESTEP = '' + @.timestep + ''
> AND TIME >= '' + @.startdatetime + ''
> AND TIME <= '' + @.enddatetime + '' '' ) Q
> '
> EXEC (@.SQLSTR)
> -- Scott|||Try to click the refresh fields button (one of the buttons to the right of
the ...)
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:%23ZUGWiOPIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Thanks to you both I got it working:
> DECLARE @.SQLSTR NVARCHAR(4000)
> SET @.SQLSTR => '
> SELECT * FROM OPENQUERY(PST,
> ''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
> work_asgn_id,
> descr, trunc(job_work_date) as job_work_date
> from EWM.ASSIGNMENT_HISTORY a
> inner join EWM.TERMINAL t
> on a.work_terminal = t.terminal
> where trunc(job_work_date) = to_date( '' + @.ForDate + '',
> ''''MM/DD/YYYY'''')' +
> ' and act_offduty_date_time is not null
> and act_onduty_date_time is not null
> and to_char(a.ASSIGNMENT_HISTORY_SID) in
> (select min(to_char(ASSIGNMENT_HISTORY_SID)) from EWM.ASSIGNMENT_HISTORY
> b
> where a.work_asgn_id = b.WORK_ASGN_ID
> and a.WORK_TERMINAL = b.WORK_TERMINAL
> and trunc(job_work_date) = to_date( '' + @.ForDate + '',
> ''''MM/DD/YYYY''''))''' +
> ')'
> EXEC (@.SQLSTR)
> I run it fine in Data tab, but in Layout when I try to assign an
> expression to a field selecting dataset it says:
> 'DailySummary' dataset has no fields.
> What's wrong?
>
> "Orne" <polysillycon@.yahoo.com> wrote in message
> news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
>> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am running OPENQUERY against Oracle database via a linked server.
>> How can I provide parameters into the Select statement?
>> Thanks
>> What you basically need to do is treat your query like you are writing
>> a String that contains your query, then EXEC the string at the end.
>> Any embeded quotation marks need to be "doubled", then you concatenate
>> your parameters using string concat symbols ( the + sign in SQL
>> Server ), then EXEC it at the end and you're golden.
>>
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY( PISERVER,
>> ''SELECT TAG, TIME, VALUE
>> FROM piarchive.piavg
>> WHERE TAG = '' + @.tagname + ''
>> AND TIMESTEP = '' + @.timestep + ''
>> AND TIME >= '' + @.startdatetime + ''
>> AND TIME <= '' + @.enddatetime + '' '' ) Q
>> '
>> EXEC (@.SQLSTR)
>> -- Scott
>|||Worked!!
I am also having infamous:
Invalid data for type "numeric".
Is there any fix available for this error?
Thanks
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ebrTNpOPIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Try to click the refresh fields button (one of the buttons to the right of
> the ...)
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mark Goldin" <mgoldin@.ufandd.com> wrote in message
> news:%23ZUGWiOPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> Thanks to you both I got it working:
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY(PST,
>> ''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
>> work_asgn_id,
>> descr, trunc(job_work_date) as job_work_date
>> from EWM.ASSIGNMENT_HISTORY a
>> inner join EWM.TERMINAL t
>> on a.work_terminal = t.terminal
>> where trunc(job_work_date) = to_date( '' + @.ForDate + '',
>> ''''MM/DD/YYYY'''')' +
>> ' and act_offduty_date_time is not null
>> and act_onduty_date_time is not null
>> and to_char(a.ASSIGNMENT_HISTORY_SID) in
>> (select min(to_char(ASSIGNMENT_HISTORY_SID)) from EWM.ASSIGNMENT_HISTORY
>> b
>> where a.work_asgn_id = b.WORK_ASGN_ID
>> and a.WORK_TERMINAL = b.WORK_TERMINAL
>> and trunc(job_work_date) = to_date( '' + @.ForDate + '',
>> ''''MM/DD/YYYY''''))''' +
>> ')'
>> EXEC (@.SQLSTR)
>> I run it fine in Data tab, but in Layout when I try to assign an
>> expression to a field selecting dataset it says:
>> 'DailySummary' dataset has no fields.
>> What's wrong?
>>
>> "Orne" <polysillycon@.yahoo.com> wrote in message
>> news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
>> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am running OPENQUERY against Oracle database via a linked server.
>> How can I provide parameters into the Select statement?
>> Thanks
>> What you basically need to do is treat your query like you are writing
>> a String that contains your query, then EXEC the string at the end.
>> Any embeded quotation marks need to be "doubled", then you concatenate
>> your parameters using string concat symbols ( the + sign in SQL
>> Server ), then EXEC it at the end and you're golden.
>>
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY( PISERVER,
>> ''SELECT TAG, TIME, VALUE
>> FROM piarchive.piavg
>> WHERE TAG = '' + @.tagname + ''
>> AND TIMESTEP = '' + @.timestep + ''
>> AND TIME >= '' + @.startdatetime + ''
>> AND TIME <= '' + @.enddatetime + '' '' ) Q
>> '
>> EXEC (@.SQLSTR)
>> -- Scott
>>
>|||I have never seen this error.
You could try creating a stored procedure. In the stored procedure create a
temp table. Then do this:
insert #yourtemptable select * from openquery(pst, @.SQLSTR)
select * from #yourtemptable
return
Note that you would have to rework your string again.
Now, you know for sure what your output types are (based on how you created
the temp table) and you can thoroughly test outside of RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:%233wMgCPPIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Worked!!
> I am also having infamous:
> Invalid data for type "numeric".
> Is there any fix available for this error?
> Thanks
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:ebrTNpOPIHA.6036@.TK2MSFTNGP03.phx.gbl...
>> Try to click the refresh fields button (one of the buttons to the right
>> of the ...)
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mark Goldin" <mgoldin@.ufandd.com> wrote in message
>> news:%23ZUGWiOPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> Thanks to you both I got it working:
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY(PST,
>> ''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
>> work_asgn_id,
>> descr, trunc(job_work_date) as job_work_date
>> from EWM.ASSIGNMENT_HISTORY a
>> inner join EWM.TERMINAL t
>> on a.work_terminal = t.terminal
>> where trunc(job_work_date) = to_date( '' + @.ForDate + '',
>> ''''MM/DD/YYYY'''')' +
>> ' and act_offduty_date_time is not null
>> and act_onduty_date_time is not null
>> and to_char(a.ASSIGNMENT_HISTORY_SID) in
>> (select min(to_char(ASSIGNMENT_HISTORY_SID)) from
>> EWM.ASSIGNMENT_HISTORY b
>> where a.work_asgn_id = b.WORK_ASGN_ID
>> and a.WORK_TERMINAL = b.WORK_TERMINAL
>> and trunc(job_work_date) = to_date( '' + @.ForDate + '',
>> ''''MM/DD/YYYY''''))''' +
>> ')'
>> EXEC (@.SQLSTR)
>> I run it fine in Data tab, but in Layout when I try to assign an
>> expression to a field selecting dataset it says:
>> 'DailySummary' dataset has no fields.
>> What's wrong?
>>
>> "Orne" <polysillycon@.yahoo.com> wrote in message
>> news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
>> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am running OPENQUERY against Oracle database via a linked server.
>> How can I provide parameters into the Select statement?
>> Thanks
>> What you basically need to do is treat your query like you are writing
>> a String that contains your query, then EXEC the string at the end.
>> Any embeded quotation marks need to be "doubled", then you concatenate
>> your parameters using string concat symbols ( the + sign in SQL
>> Server ), then EXEC it at the end and you're golden.
>>
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY( PISERVER,
>> ''SELECT TAG, TIME, VALUE
>> FROM piarchive.piavg
>> WHERE TAG = '' + @.tagname + ''
>> AND TIMESTEP = '' + @.timestep + ''
>> AND TIME >= '' + @.startdatetime + ''
>> AND TIME <= '' + @.enddatetime + '' '' ) Q
>> '
>> EXEC (@.SQLSTR)
>> -- Scott
>>
>>
>|||On Dec 12, 2:12 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have never seen this error.
> You could try creating a stored procedure. In the stored procedure create a
> temp table. Then do this:
> insert #yourtemptable select * from openquery(pst, @.SQLSTR)
> select * from #yourtemptable
> return
> Note that you would have to rework your string again.
> Now, you know for sure what your output types are (based on how you created
> the temp table) and you can thoroughly test outside of RS.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mark Goldin" <mgol...@.ufandd.com> wrote in message
> news:%233wMgCPPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>
> > Worked!!
> > I am also having infamous:
> > Invalid data for type "numeric".
> > Is there any fix available for this error?
> > Thanks
> > "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com> wrote in message
> >news:ebrTNpOPIHA.6036@.TK2MSFTNGP03.phx.gbl...
> >> Try to click the refresh fields button (one of the buttons to the right
> >> of the ...)
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> "Mark Goldin" <mgol...@.ufandd.com> wrote in message
> >>news:%23ZUGWiOPIHA.5400@.TK2MSFTNGP04.phx.gbl...
> >> Thanks to you both I got it working:
> >> DECLARE @.SQLSTR NVARCHAR(4000)
> >> SET @.SQLSTR => >> '
> >> SELECT * FROM OPENQUERY(PST,
> >> ''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
> >> work_asgn_id,
> >> descr, trunc(job_work_date) as job_work_date
> >> from EWM.ASSIGNMENT_HISTORY a
> >> inner join EWM.TERMINAL t
> >> on a.work_terminal = t.terminal
> >> where trunc(job_work_date) = to_date( '' + @.ForDate + '',
> >> ''''MM/DD/YYYY'''')' +
> >> ' and act_offduty_date_time is not null
> >> and act_onduty_date_time is not null
> >> and to_char(a.ASSIGNMENT_HISTORY_SID) in
> >> (select min(to_char(ASSIGNMENT_HISTORY_SID)) from
> >> EWM.ASSIGNMENT_HISTORY b
> >> where a.work_asgn_id = b.WORK_ASGN_ID
> >> and a.WORK_TERMINAL = b.WORK_TERMINAL
> >> and trunc(job_work_date) = to_date( '' + @.ForDate + '',
> >> ''''MM/DD/YYYY''''))''' +
> >> ')'
> >> EXEC (@.SQLSTR)
> >> I run it fine in Data tab, but in Layout when I try to assign an
> >> expression to a field selecting dataset it says:
> >> 'DailySummary' dataset has no fields.
> >> What's wrong?
> >> "Orne" <polysilly...@.yahoo.com> wrote in message
> >>news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
> >> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> >> I am running OPENQUERY against Oracle database via a linked server.
> >> How can I provide parameters into the Select statement?
> >> Thanks
> >> What you basically need to do is treat your query like you are writing
> >> a String that contains your query, then EXEC the string at the end.
> >> Any embeded quotation marks need to be "doubled", then you concatenate
> >> your parameters using string concat symbols ( the + sign in SQL
> >> Server ), then EXEC it at the end and you're golden.
> >> DECLARE @.SQLSTR NVARCHAR(4000)
> >> SET @.SQLSTR => >> '
> >> SELECT * FROM OPENQUERY( PISERVER,
> >> ''SELECT TAG, TIME, VALUE
> >> FROM piarchive.piavg
> >> WHERE TAG = '' + @.tagname + ''
> >> AND TIMESTEP = '' + @.timestep + ''
> >> AND TIME >= '' + @.startdatetime + ''
> >> AND TIME <= '' + @.enddatetime + '' '' ) Q
> >> '
> >> EXEC (@.SQLSTR)
> >> -- Scott- Hide quoted text -
> - Show quoted text -
Sounds like your work is like mine... SQL Server managing linked
servers to Oracle servers of all flavors...
Your @.ForDate parameter is a DateTime, but when it is passed as a
parameter, the formatting matters. My guess is that you are running
into errors with the parsing of either the TO_CHAR function or the
TO_DATE function.
Go to the Dataset Properties button [...], and goto the Parameters
tab. Change the Expression for ForDate from:
=Parameters!ForDate.Value
to
=Format( CDate( Parameters!ForDate.Value ), "MM/dd/yyyy" )
This will take your DateTime parameter and convert it into a String in
MM/DD/YYYY format, then the string will be concatenated to the rest of
the string and executed in SQL Server, which will pass through the SQL
query to Oracle and the TO_DATE function will always parse correctly.
The next item is that TO_CHAR( ASSIGNMENT_HISTORY_SID ) thing that you
got going everywhere. I would make sure that the
ASSIGNMENT_HISTORY_FIELD always contains a convertable number...
-- Scott|||On Dec 12, 10:12 am, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> > I am running OPENQUERY against Oracle database via a linked server.
> > How can I provide parameters into the Select statement?
> > Thanks
> What you basically need to do is treat your query like you are writing
> a String that contains your query, then EXEC the string at the end.
> Any embeded quotation marks need to be "doubled", then you concatenate
> your parameters using string concat symbols ( the + sign in SQL
> Server ), then EXEC it at the end and you're golden.
> DECLARE @.SQLSTR NVARCHAR(4000)
> SET @.SQLSTR => '
> SELECT * FROM OPENQUERY( PISERVER,
> ''SELECT TAG, TIME, VALUE
> FROM piarchive.piavg
> WHERE TAG = '' + @.tagname + ''
> AND TIMESTEP = '' + @.timestep + ''
> AND TIME >= '' + @.startdatetime + ''
> AND TIME <= '' + @.enddatetime + '' '' ) Q
> '
> EXEC (@.SQLSTR)
> -- Scott
Hi Orhne,
I was trying to do the same thing what you were trying, but I was not
able to syccessfully insert a parameter into my SQL query for Oracle
database.
--
(DSS_CLIN.V_CLAIM_PAID.BATCH_DATE between to_date(''11/01/2007'',''mm/
dd/yyyy'') and to_date(''11/07/2007'',''mm/dd/yyyy''))
--
This line should be parameterized. 11/01/2007 should be start date and
11/07/2007 is supposed to be the end date.
Please let me know on how to solve this issue, I am going to
incorporate this in SQL Reporting for generating reports. Please let
me know ASAP.
Thanks a lot.|||On Dec 12, 7:13 pm, tharani.mahend...@.gmail.com wrote:
> On Dec 12, 10:12 am, Orne <polysilly...@.yahoo.com> wrote:
>
>
> > On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> > > I am running OPENQUERY against Oracle database via a linked server.
> > > How can I provide parameters into the Select statement?
> > > Thanks
> > What you basically need to do is treat your query like you are writing
> > a String that contains your query, then EXEC the string at the end.
> > Any embeded quotation marks need to be "doubled", then you concatenate
> > your parameters using string concat symbols ( the + sign in SQL
> > Server ), then EXEC it at the end and you're golden.
> > DECLARE @.SQLSTR NVARCHAR(4000)
> > SET @.SQLSTR => > '
> > SELECT * FROM OPENQUERY( PISERVER,
> > ''SELECT TAG, TIME, VALUE
> > FROM piarchive.piavg
> > WHERE TAG = '' + @.tagname + ''
> > AND TIMESTEP = '' + @.timestep + ''
> > AND TIME >= '' + @.startdatetime + ''
> > AND TIME <= '' + @.enddatetime + '' '' ) Q
> > '
> > EXEC (@.SQLSTR)
> > -- Scott
> Hi Orhne,
> I was trying to do the same thing what you were trying, but I was not
> able to syccessfully insert a parameter into my SQL query for Oracle
> database.
> --
> (DSS_CLIN.V_CLAIM_PAID.BATCH_DATE between to_date(''11/01/2007'',''mm/
> dd/yyyy'') and to_date(''11/07/2007'',''mm/dd/yyyy''))
> --
> This line should be parameterized. 11/01/2007 should be start date and
> 11/07/2007 is supposed to be the end date.
> Please let me know on how to solve this issue, I am going to
> incorporate this in SQL Reporting for generating reports. Please let
> me know ASAP.
> Thanks a lot.- Hide quoted text -
> - Show quoted text -
Try this, with quadruple single quotes. This first level is the '
that build the string, anything in that has to be doubled. You then
have another ' for the OPENQUERY function, so every quote in that has
to be doubled again:
SET @.SQLSTR = '
SELECT * FROM OPENQUERY( LINKEDSERVERNAME, ''
SELECT * FROM DSS_CLIN.V_CLAIM_PAID
WHERE V_CLAIM_PAID.BATCH_DATE
BETWEEN TO_DATE( '' + @.StartDate + '', ''''MM/DD/YYYY'''' )
AND TO_DATE( '' + @.EndDate + '', ''''MM/DD/YYYY'''' )
'' ) '
-- Scott|||I haven't had to do this for awhile because the code is stable but I wrote a
bunch of stored procedures to maintain a datamart. I was extracting data
from Sybase using linked servers (and unfortunately in SQL 2000 you had to
use openquery because four part naming was so awful). Anyway, lots and lots
of counting of single quotes.
One other point if on SQL 2005. If doing normal SQL statements, i.e. not any
Oracle extensions, then four part naming might work.
SELECT * FROM linkedservername.database.owner.tablename WHERE
V_CLAIM_PAID.BATCH_DATE
BETWEEN @.StartDate AND @.EndDate
In SQL 2000 this statement might have pulled all the records over. In SQL
2005 it realizes everything resides on the remote server and sends the whole
query over. You can use the queryplan statement to see if this is true
before running. Joins will work etc doing this. Always check query plan
first though.
I suggest in your work checking it out, there was such a dramatic difference
between versions with how well the four part naming worked.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Orne" <polysillycon@.yahoo.com> wrote in message
news:f34d6b78-9e05-4bc4-ba3e-05e2e8dc06db@.d21g2000prf.googlegroups.com...
> On Dec 12, 7:13 pm, tharani.mahend...@.gmail.com wrote:
Snip
> Try this, with quadruple single quotes. This first level is the '
> that build the string, anything in that has to be doubled. You then
> have another ' for the OPENQUERY function, so every quote in that has
> to be doubled again:
> SET @.SQLSTR = '
> SELECT * FROM OPENQUERY( LINKEDSERVERNAME, ''
> SELECT * FROM DSS_CLIN.V_CLAIM_PAID
> WHERE V_CLAIM_PAID.BATCH_DATE
> BETWEEN TO_DATE( '' + @.StartDate + '', ''''MM/DD/YYYY'''' )
> AND TO_DATE( '' + @.EndDate + '', ''''MM/DD/YYYY'''' )
> '' ) '
> -- Scott|||On Dec 13, 8:50 am, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 12, 7:13 pm, tharani.mahend...@.gmail.com wrote:
>
>
> > On Dec 12, 10:12 am, Orne <polysilly...@.yahoo.com> wrote:
> > > On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> > > > I am running OPENQUERY against Oracle database via a linked server.
> > > > How can I provide parameters into the Select statement?
> > > > Thanks
> > > What you basically need to do is treat your query like you are writing
> > > a String that contains your query, then EXEC the string at the end.
> > > Any embeded quotation marks need to be "doubled", then you concatenate
> > > your parameters using string concat symbols ( the + sign in SQL
> > > Server ), then EXEC it at the end and you're golden.
> > > DECLARE @.SQLSTR NVARCHAR(4000)
> > > SET @.SQLSTR => > > '
> > > SELECT * FROM OPENQUERY( PISERVER,
> > > ''SELECT TAG, TIME, VALUE
> > > FROM piarchive.piavg
> > > WHERE TAG = '' + @.tagname + ''
> > > AND TIMESTEP = '' + @.timestep + ''
> > > AND TIME >= '' + @.startdatetime + ''
> > > AND TIME <= '' + @.enddatetime + '' '' ) Q
> > > '
> > > EXEC (@.SQLSTR)
> > > -- Scott
> > Hi Orhne,
> > I was trying to do the same thing what you were trying, but I was not
> > able to syccessfully insert a parameter into my SQL query for Oracle
> > database.
> > --
> > (DSS_CLIN.V_CLAIM_PAID.BATCH_DATE between to_date(''11/01/2007'',''mm/
> > dd/yyyy'') and to_date(''11/07/2007'',''mm/dd/yyyy''))
> > --
> > This line should be parameterized. 11/01/2007 should be start date and
> > 11/07/2007 is supposed to be the end date.
> > Please let me know on how to solve this issue, I am going to
> > incorporate this in SQL Reporting for generating reports. Please let
> > me know ASAP.
> > Thanks a lot.- Hide quoted text -
> > - Show quoted text -
> Try this, with quadruple single quotes. This first level is the '
> that build the string, anything in that has to be doubled. You then
> have another ' for the OPENQUERY function, so every quote in that has
> to be doubled again:
> SET @.SQLSTR = '
> SELECT * FROM OPENQUERY( LINKEDSERVERNAME, ''
> SELECT * FROM DSS_CLIN.V_CLAIM_PAID
> WHERE V_CLAIM_PAID.BATCH_DATE
> BETWEEN TO_DATE( '' + @.StartDate + '', ''''MM/DD/YYYY'''' )
> AND TO_DATE( '' + @.EndDate + '', ''''MM/DD/YYYY'''' )
> '' ) '
> -- Scott- Hide quoted text -
> - Show quoted text -
This is the error which I am getting "Must declare the scalar variable
"@.StartDate".

Opening RS HomePage is Extermly slow! Please help.

When a client types in the homepage of the RS it takes minutes to open
instead of seconds. It is running on a W2K3 machine with SQL 2000 and the
clients are a mix of XP and 2K Pro. Any advice would be great. Oh this also
happens on the server itself.
Once you open it once it opens in seconds from then on until you logoff and
back on to the pc again. Then it is slow again.
Thanks!This was discussed in the forum several months ago. You can find the
discussion by going to
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx and do a
search on the text "First Time Delay on Client Systems".
"Andy Jones" <ajones@.rheemac.com> wrote in message
news:eJAhsvihFHA.2424@.TK2MSFTNGP09.phx.gbl...
> When a client types in the homepage of the RS it takes minutes to open
> instead of seconds. It is running on a W2K3 machine with SQL 2000 and the
> clients are a mix of XP and 2K Pro. Any advice would be great. Oh this
also
> happens on the server itself.
> Once you open it once it opens in seconds from then on until you logoff
and
> back on to the pc again. Then it is slow again.
> Thanks!
>|||Which specific post? There are so many, I'm not sure which one applies...
"Vince Sefcik" wrote:
> This was discussed in the forum several months ago. You can find the
> discussion by going to
> http://www.microsoft.com/communities/newsgroups/en-us/default.aspx and do a
> search on the text "First Time Delay on Client Systems".
> "Andy Jones" <ajones@.rheemac.com> wrote in message
> news:eJAhsvihFHA.2424@.TK2MSFTNGP09.phx.gbl...
> > When a client types in the homepage of the RS it takes minutes to open
> > instead of seconds. It is running on a W2K3 machine with SQL 2000 and the
> > clients are a mix of XP and 2K Pro. Any advice would be great. Oh this
> also
> > happens on the server itself.
> >
> > Once you open it once it opens in seconds from then on until you logoff
> and
> > back on to the pc again. Then it is slow again.
> >
> > Thanks!
> >
> >
>
>|||Here is a suggestion from Chris that worked for him. I was unsuccessful with
this. What I do is I have a simple report that I have automatically refresh
every 5 minutes (set this in Report Properties).
>>>>>>>>>>
If you are running Windows 2003 server for your IIS reportserver, then this
is a simple issue - I'll explain what happens:
The report service engine, once it is idle for more than the default 20
minutes, the worker process is shutdown.
This is controlled by IIS.
Open up the Internet Information Services (IIS) Manager
Expand the server node then the application pools.
On my IIS machine, I created an application pool dedicated to the
reportserver & reportmanager virtual webs.
But anyways, for the application pool that the reportserver is pointing to
if you left everything to their defaults will be the DefaultAppPool.
Right click the default app pool and select properties.
There are two things that are checked by default - On the recycling tab
there is a checkbox for recycling worker processes - it is currently set to
1740 minutes (29 hours). Leave it.
The other one is on the performance tab - which is the one you are
interested in changing...
See the "Idle Timeout" section and increase the number of minutes to be 8
hours a typical working day - 8*60 = 480 minutes.
Next, to be sure the "morning person" that runs the first report doesn't get
the delay, set up a schedule for either a dummy or adhoc report to fire off
like at 6am so that the report component worker processes get loaded.
I hope this helps you.
There is no need to have a report fire off every minute to keep things
alive - it is just that the report service was "unloaded" and needed to load
back up.
=-Chris
>>>>>>>>>>>>>>>'
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MustangDJB" <MustangDJB@.discussions.microsoft.com> wrote in message
news:E12C4DCA-AF89-4770-9649-6E0775FA42ED@.microsoft.com...
> Which specific post? There are so many, I'm not sure which one
> applies...
> "Vince Sefcik" wrote:
>> This was discussed in the forum several months ago. You can find the
>> discussion by going to
>> http://www.microsoft.com/communities/newsgroups/en-us/default.aspx and do
>> a
>> search on the text "First Time Delay on Client Systems".
>> "Andy Jones" <ajones@.rheemac.com> wrote in message
>> news:eJAhsvihFHA.2424@.TK2MSFTNGP09.phx.gbl...
>> > When a client types in the homepage of the RS it takes minutes to open
>> > instead of seconds. It is running on a W2K3 machine with SQL 2000 and
>> > the
>> > clients are a mix of XP and 2K Pro. Any advice would be great. Oh this
>> also
>> > happens on the server itself.
>> >
>> > Once you open it once it opens in seconds from then on until you logoff
>> and
>> > back on to the pc again. Then it is slow again.
>> >
>> > Thanks!
>> >
>> >
>>|||Thank you! That does appear to help.
"Bruce L-C [MVP]" wrote:
> Here is a suggestion from Chris that worked for him. I was unsuccessful with
> this. What I do is I have a simple report that I have automatically refresh
> every 5 minutes (set this in Report Properties).
> >>>>>>>>>>
> If you are running Windows 2003 server for your IIS reportserver, then this
> is a simple issue - I'll explain what happens:
> The report service engine, once it is idle for more than the default 20
> minutes, the worker process is shutdown.
> This is controlled by IIS.
> Open up the Internet Information Services (IIS) Manager
> Expand the server node then the application pools.
> On my IIS machine, I created an application pool dedicated to the
> reportserver & reportmanager virtual webs.
> But anyways, for the application pool that the reportserver is pointing to
> if you left everything to their defaults will be the DefaultAppPool.
> Right click the default app pool and select properties.
> There are two things that are checked by default - On the recycling tab
> there is a checkbox for recycling worker processes - it is currently set to
> 1740 minutes (29 hours). Leave it.
> The other one is on the performance tab - which is the one you are
> interested in changing...
> See the "Idle Timeout" section and increase the number of minutes to be 8
> hours a typical working day - 8*60 = 480 minutes.
> Next, to be sure the "morning person" that runs the first report doesn't get
> the delay, set up a schedule for either a dummy or adhoc report to fire off
> like at 6am so that the report component worker processes get loaded.
> I hope this helps you.
> There is no need to have a report fire off every minute to keep things
> alive - it is just that the report service was "unloaded" and needed to load
> back up.
> =-Chris
> >>>>>>>>>>>>>>>'
>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
>
> "MustangDJB" <MustangDJB@.discussions.microsoft.com> wrote in message
> news:E12C4DCA-AF89-4770-9649-6E0775FA42ED@.microsoft.com...
> > Which specific post? There are so many, I'm not sure which one
> > applies...
> > "Vince Sefcik" wrote:
> >
> >> This was discussed in the forum several months ago. You can find the
> >> discussion by going to
> >> http://www.microsoft.com/communities/newsgroups/en-us/default.aspx and do
> >> a
> >> search on the text "First Time Delay on Client Systems".
> >>
> >> "Andy Jones" <ajones@.rheemac.com> wrote in message
> >> news:eJAhsvihFHA.2424@.TK2MSFTNGP09.phx.gbl...
> >> > When a client types in the homepage of the RS it takes minutes to open
> >> > instead of seconds. It is running on a W2K3 machine with SQL 2000 and
> >> > the
> >> > clients are a mix of XP and 2K Pro. Any advice would be great. Oh this
> >> also
> >> > happens on the server itself.
> >> >
> >> > Once you open it once it opens in seconds from then on until you logoff
> >> and
> >> > back on to the pc again. Then it is slow again.
> >> >
> >> > Thanks!
> >> >
> >> >
> >>
> >>
> >>
>
>sql

opening ADP combo box causes high CPU utilization on SQL Server; query never completes

Hello all,
I'm using an Access 2000 (9.0.6926 SP-3) ADP as a front-end to a SQL Server
2000 (8.00.760 SP-3) database. SQL Server is running on a dedicated server.
There is a certain query, based on a view, that completes in about two
seconds via Query Analyzer. (It's a straightforward SELECT ... FROM
view_name WHERE ...; I'll spare you the code since the view does end up
hitting many tables, and I'm mostly curious whether this is a well-known bug
I wasn't able to find.) The query returns less than 300 rows.
We're using that query as the Row Source for a combo box in the ADP
front-end. When attempting to expand the combo box, the ADP seems to
freeze. CPU utilization for sqlservr.exe jumps to around 99. Firing up
Profiler beforehand and doing a trace on SQL Server shows a
SQL:BatchStarting and SQL:StmtStarting, with the TextData being the SELECT,
as I'd expect.
Eventually, if I kill the ADP (via task manager), the trace shows a
SQL:BatchCompleted with high numbers for CPU and Reads (unscientifically, it
seems that the longer I let the ADP run, the higher the numbers). There is,
of course, no SQL:StmtCompleted. I'm inclined to believe that, somehow, the
way that Access submits the query is different from Query Analyzer and is
causing SQL Server to go into an infinite loop of some kind.
I'm hoping this is a straightforward, known issue. I'd rather not open a
call with Microsoft; the beauracracy here with providing the up-front fee
(even if it is refunded) would make doing so very painful and difficult. I
can provide more back-end code, the execution plans, trace information,
etc., if anyone takes an interest. Thanks in advance for any help!
Regards,
Brian J. Parker
Systems Coordinator, Childhood Depression Research
Western Psychiatric Institute and Clinic, UPMC Health SystemA little follow-up on this problem:
The view on which the hanging SELECT is based includes a join between two
large tables; they were being joined on a shared derived (text) column
because the view writer didn't want to type out all the underlying integer
columns instead.
I re-wrote the view to use "real" columns and the problem vanishes (which
takes away some of the urgency). My original issue is, I think, still
valid-- a query that takes six seconds in Query Analyzer shouldn't run
indefinitely as the row source of a combo box-- but this may be a clue.
Regards,
Brian|||This would depend on how the data is retrieved for the combo box. Is it a bound control? That could be adding some overhead on the query being sent to SQL Server.|||"Doug Guerena" <anonymous@.discussions.microsoft.com> wrote in message
news:B676064D-81AB-43FB-AE1C-23EC93FCC069@.microsoft.com...
> This would depend on how the data is retrieved for the combo box. Is it a
bound control? That could be adding some overhead on the query being sent
to SQL Server.
No, it's not a bound control, but thanks for the suggestion.
If you're curious: the control provides a list of research study interviews
meeting certain criteria. Based on the user's selection, we launch a report
that provides some information related to that interview.
Regards,
Brian

opening ADP combo box causes high CPU utilization on SQL Server; query never completes

Hello all,
I'm using an Access 2000 (9.0.6926 SP-3) ADP as a front-end to a SQL Server
2000 (8.00.760 SP-3) database. SQL Server is running on a dedicated server.
There is a certain query, based on a view, that completes in about two
seconds via Query Analyzer. (It's a straightforward SELECT ... FROM
view_name WHERE ...; I'll spare you the code since the view does end up
hitting many tables, and I'm mostly curious whether this is a well-known bug
I wasn't able to find.) The query returns less than 300 rows.
We're using that query as the Row Source for a combo box in the ADP
front-end. When attempting to expand the combo box, the ADP seems to
freeze. CPU utilization for sqlservr.exe jumps to around 99. Firing up
Profiler beforehand and doing a trace on SQL Server shows a
SQL:BatchStarting and SQL:StmtStarting, with the TextData being the SELECT,
as I'd expect.
Eventually, if I kill the ADP (via task manager), the trace shows a
SQL:BatchCompleted with high numbers for CPU and Reads (unscientifically, it
seems that the longer I let the ADP run, the higher the numbers). There is,
of course, no SQL:StmtCompleted. I'm inclined to believe that, somehow, the
way that Access submits the query is different from Query Analyzer and is
causing SQL Server to go into an infinite loop of some kind.
I'm hoping this is a straightforward, known issue. I'd rather not open a
call with Microsoft; the beauracracy here with providing the up-front fee
(even if it is refunded) would make doing so very painful and difficult. I
can provide more back-end code, the execution plans, trace information,
etc., if anyone takes an interest. Thanks in advance for any help!
Regards,
Brian J. Parker
Systems Coordinator, Childhood Depression Research
Western Psychiatric Institute and Clinic, UPMC Health SystemA little follow-up on this problem:
The view on which the hanging SELECT is based includes a join between two
large tables; they were being joined on a shared derived (text) column
because the view writer didn't want to type out all the underlying integer
columns instead.
I re-wrote the view to use "real" columns and the problem vanishes (which
takes away some of the urgency). My original issue is, I think, still
valid-- a query that takes six seconds in Query Analyzer shouldn't run
indefinitely as the row source of a combo box-- but this may be a clue.
Regards,
Brian|||This would depend on how the data is retrieved for the combo box. Is it a b
ound control? That could be adding some overhead on the query being sent to
SQL Server.|||"Doug Guerena" <anonymous@.discussions.microsoft.com> wrote in message
news:B676064D-81AB-43FB-AE1C-23EC93FCC069@.microsoft.com...
> This would depend on how the data is retrieved for the combo box. Is it a
bound control? That could be adding some overhead on the query being sent
to SQL Server.
No, it's not a bound control, but thanks for the suggestion.
If you're curious: the control provides a list of research study interviews
meeting certain criteria. Based on the user's selection, we launch a report
that provides some information related to that interview.
Regards,
Brian

OPENDATASOURCE Problems (permission)

Hi all,
I am running SQL2000 SP4 and want to run the command:
SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
with members"
logged in as "sa" it is fine but when another user no "sa" runs I get:
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied.
You must access this provider through a linked server.
Can anyone help me? The user that will run that is not "sa"
thanks
JulioJulio
http://support.microsoft.com/kb/327489
"Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
news:732AD50A-E369-49E4-AE60-A5256075C37B@.microsoft.com...
> Hi all,
> I am running SQL2000 SP4 and want to run the command:
> SELECT *
> FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
> Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
> with members"
> logged in as "sa" it is fine but when another user no "sa" runs I get:
> Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been
> denied.
> You must access this provider through a linked server.
> Can anyone help me? The user that will run that is not "sa"
> thanks
> Julio|||Hi Uri,
thanks for that, I created a new registry entry (DisallowAdHocAccess) under
'Microsoft Jet 4.0 OLE DB Provider' set to 0 and it's working fine. The key
is the DisallowAdHocAccess for each provider.
thanks
Julio M
"Uri Dimant" wrote:
> Julio
> http://support.microsoft.com/kb/327489
>
> "Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
> news:732AD50A-E369-49E4-AE60-A5256075C37B@.microsoft.com...
> > Hi all,
> >
> > I am running SQL2000 SP4 and want to run the command:
> >
> > SELECT *
> > FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
> > Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
> > with members"
> >
> > logged in as "sa" it is fine but when another user no "sa" runs I get:
> >
> > Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been
> > denied.
> > You must access this provider through a linked server.
> >
> > Can anyone help me? The user that will run that is not "sa"
> >
> > thanks
> > Julio
>
>

OPENDATASOURCE Problems (permission)

Hi all,
I am running SQL2000 SP4 and want to run the command:
SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
with members"
logged in as "sa" it is fine but when another user no "sa" runs I get:
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied.
You must access this provider through a linked server.
Can anyone help me? The user that will run that is not "sa"
thanks
Julio
Julio
http://support.microsoft.com/kb/327489
"Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
news:732AD50A-E369-49E4-AE60-A5256075C37B@.microsoft.com...
> Hi all,
> I am running SQL2000 SP4 and want to run the command:
> SELECT *
> FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
> Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
> with members"
> logged in as "sa" it is fine but when another user no "sa" runs I get:
> Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been
> denied.
> You must access this provider through a linked server.
> Can anyone help me? The user that will run that is not "sa"
> thanks
> Julio
|||Hi Uri,
thanks for that, I created a new registry entry (DisallowAdHocAccess) under
'Microsoft Jet 4.0 OLE DB Provider' set to 0 and it's working fine. The key
is the DisallowAdHocAccess for each provider.
thanks
Julio M
"Uri Dimant" wrote:

> Julio
> http://support.microsoft.com/kb/327489
>
> "Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
> news:732AD50A-E369-49E4-AE60-A5256075C37B@.microsoft.com...
>
>

OPENDATASOURCE Problems (permission)

Hi all,
I am running SQL2000 SP4 and want to run the command:
SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
with members"
logged in as "sa" it is fine but when another user no "sa" runs I get:
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied.
You must access this provider through a linked server.
Can anyone help me? The user that will run that is not "sa"
thanks
JulioJulio
http://support.microsoft.com/kb/327489
"Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
news:732AD50A-E369-49E4-AE60-A5256075C37B@.microsoft.com...
> Hi all,
> I am running SQL2000 SP4 and want to run the command:
> SELECT *
> FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data
> Source="f:\jackpot\jackpot.mdb"; User ID=Admin;Password=')..."master table
> with members"
> logged in as "sa" it is fine but when another user no "sa" runs I get:
> Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been
> denied.
> You must access this provider through a linked server.
> Can anyone help me? The user that will run that is not "sa"
> thanks
> Julio|||Hi Uri,
thanks for that, I created a new registry entry (DisallowAdHocAccess) under
'Microsoft Jet 4.0 OLE DB Provider' set to 0 and it's working fine. The key
is the DisallowAdHocAccess for each provider.
thanks
Julio M
"Uri Dimant" wrote:

> Julio
> http://support.microsoft.com/kb/327489
>
> "Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
> news:732AD50A-E369-49E4-AE60-A5256075C37B@.microsoft.com...
>
>

Open_tran in sys.sysprocesses has unexpected count

I'm running SQL Server 2005 SP1 on WinXP SP2.

I'm running a query to look for open transactions or blocked transactions. The actual query I'm running registers as having 2 open transactions and I can't figure out why. It seems to have something to do with the temp table because I don't get the open transactions when there is no temp table. I could perhaps see why I might have 1 open transaction pertaining to the open temp table, but why 2. Here is the query and the data from sys.sysprocesses:

IF object_id('tempdb.dbo.#MyProcesses','U') is not NULL
BEGIN DROP table #MyProcesses; END
DECLARE @.MyVariable varchar(100)
, @.Mycmd nvarchar(1000)
, @.LoginTime int
, @.LastBatch int
, @.LastBatchFilter datetime
, @.LoginTimeFilter datetime;
SELECT @.MyVariable = '' , @.LoginTime = -600 , @.LastBatch = -145 ;

SELECT @.LastBatchFilter = DATEADD(mi,@.LastBatch,Current_timestamp);
SELECT @.LoginTimeFilter = DATEADD(mi,@.LoginTime,Current_timestamp);

-- I've reduced the number of columns in my SELECT for this example
SELECT b.[name] MyDB
, a.spid
, a.blocked
, a.open_tran
, RTRIM(a.program_name) program_name
INTO #MyProcesses
FROM sys.sysprocesses a
JOIN sys.sysdatabases b on a.dbid = b.dbid
WHERE (a.blocked = 1 or a.open_tran > 0);

Select * from #MyProcesses

MyDB spid blocked open_tran program_name
master 56 0 2 Microsoft SQL Server Management Studio - Query

Please educate me.

Thanks,

Paul

The "transaction count" is not really that important. There are many things which implicitly create transactions. The important item is "blocked".

In your case, creating a temp table with an @.tablename creates a transaction around the query, so when it ends, it "rolls back" the temp table and deletes it. This is an internal mechanisim and not under your control.sql

Open_tran in sys.sysprocesses has unexpected count

I'm running SQL Server 2005 SP1 on WinXP SP2.

I'm running a query to look for open transactions or blocked transactions. The actual query I'm running registers as having 2 open transactions and I can't figure out why. It seems to have something to do with the temp table because I don't get the open transactions when there is no temp table. I could perhaps see why I might have 1 open transaction pertaining to the open temp table, but why 2. Here is the query and the data from sys.sysprocesses:

IF object_id('tempdb.dbo.#MyProcesses','U') is not NULL
BEGIN DROP table #MyProcesses; END
DECLARE @.MyVariable varchar(100)
, @.Mycmd nvarchar(1000)
, @.LoginTime int
, @.LastBatch int
, @.LastBatchFilter datetime
, @.LoginTimeFilter datetime;
SELECT @.MyVariable = '' , @.LoginTime = -600 , @.LastBatch = -145 ;

SELECT @.LastBatchFilter = DATEADD(mi,@.LastBatch,Current_timestamp);
SELECT @.LoginTimeFilter = DATEADD(mi,@.LoginTime,Current_timestamp);

-- I've reduced the number of columns in my SELECT for this example
SELECT b.[name] MyDB
, a.spid
, a.blocked
, a.open_tran
, RTRIM(a.program_name) program_name
INTO #MyProcesses
FROM sys.sysprocesses a
JOIN sys.sysdatabases b on a.dbid = b.dbid
WHERE (a.blocked = 1 or a.open_tran > 0);

Select * from #MyProcesses

MyDB spid blocked open_tran program_name
master 56 0 2 Microsoft SQL Server Management Studio - Query

Please educate me.

Thanks,

Paul

The "transaction count" is not really that important. There are many things which implicitly create transactions. The important item is "blocked".

In your case, creating a temp table with an @.tablename creates a transaction around the query, so when it ends, it "rolls back" the temp table and deletes it. This is an internal mechanisim and not under your control.

only 1 out of 8 CPU maxed out

Hello
I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
running just fine but only one out of the 8 CPUs is running at 90%+
constantly. So avg is OK bit our alerting doesn't understand this.
Any way of determining what is running on that 1 CPU? This does not look
normal.
We dont have CPU affinitised and SQL Server is the only app on the box.
Are there some relevant perfmon counters I can check
tks
-- cranfield, DBA
This is not unusual. You have a SQL process that is not parallelizable.
Probably something with derived columns, cursors, or intense calculations.
Those are the "usual suspects" when it comes to single-threaded apps. SQL
runs processes across multiple processors where possible, but it is not
always possuble.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
> Hello
> I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
> running just fine but only one out of the 8 CPUs is running at 90%+
> constantly. So avg is OK bit our alerting doesn't understand this.
> Any way of determining what is running on that 1 CPU? This does not look
> normal.
> We dont have CPU affinitised and SQL Server is the only app on the box.
> Are there some relevant perfmon counters I can check
> tks
> --
> -- cranfield, DBA
|||Thanks for the reply.
We have complete mirrored/parallel systems and on ServerB (which is
identical to the ServerA in question) we see similar CPU profile where 1 of
the CPUs spikes. Its not as pronounced as ServerA and load seems to be much
more evenly shared across the CPUs.
ServerA - CPU 0,1,2,3,4,5,7 - avg 5-10%; CPU6 - avg 90%
ServerB - CPU 0,1,2,3,4,5,7 - avg 10-30%; CPU6 - avg 60%
* exact same hardware
* exact same load (mostly continuous, batched, 1024 row bulk inserts)
*
I wonder what is causing the difference. Obviously I would prefer them to
use all CPUs.
-- cranfield, DBA
"Geoff N. Hiten" wrote:

> This is not unusual. You have a SQL process that is not parallelizable.
> Probably something with derived columns, cursors, or intense calculations.
> Those are the "usual suspects" when it comes to single-threaded apps. SQL
> runs processes across multiple processors where possible, but it is not
> always possuble.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
>
|||If it's always the same CPU I'd be a bit suspicious. Check for an affinity
mask. What Sql Server product/licence are you using?
We get worse performance with parallel query. Although we have maxdup at 1
we still use all 8 cpus.
Regards
Paul Cahill
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:563A3C37-DDD5-4816-8898-C74966E33C00@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply.
> We have complete mirrored/parallel systems and on ServerB (which is
> identical to the ServerA in question) we see similar CPU profile where 1
> of
> the CPUs spikes. Its not as pronounced as ServerA and load seems to be
> much
> more evenly shared across the CPUs.
> ServerA - CPU 0,1,2,3,4,5,7 - avg 5-10%; CPU6 - avg 90%
> ServerB - CPU 0,1,2,3,4,5,7 - avg 10-30%; CPU6 - avg 60%
> * exact same hardware
> * exact same load (mostly continuous, batched, 1024 row bulk inserts)
> *
> I wonder what is causing the difference. Obviously I would prefer them to
> use all CPUs.
> --
> -- cranfield, DBA
>
> "Geoff N. Hiten" wrote:
|||What version of SQL Server and Service pack? The way in which the
connections are tied to schedulers is different in both with 2000 having
more issues. How many concurrent connections do you have and what processors
are most of them on?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
> Hello
> I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
> running just fine but only one out of the 8 CPUs is running at 90%+
> constantly. So avg is OK bit our alerting doesn't understand this.
> Any way of determining what is running on that 1 CPU? This does not look
> normal.
> We dont have CPU affinitised and SQL Server is the only app on the box.
> Are there some relevant perfmon counters I can check
> tks
> --
> -- cranfield, DBA
|||morning!
Both servers on SP2 + hotfix:
SQL Server 2005 - 9.00.3152.00 (Intel X86) Enterprise Edition on Windows NT
5.2 (Build 3790: Service Pack 2)
* sp_who2 shows 69 SPIDs (28 active)
How do I know which processers my connections are on?
tks
-- cranfield, DBA
"Andrew J. Kelly" wrote:

> What version of SQL Server and Service pack? The way in which the
> connections are tied to schedulers is different in both with 2000 having
> more issues. How many concurrent connections do you have and what processors
> are most of them on?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
>

only 1 out of 8 CPU maxed out

Hello
I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
running just fine but only one out of the 8 CPUs is running at 90%+
constantly. So avg is OK bit our alerting doesn't understand this.
Any way of determining what is running on that 1 CPU? This does not look
normal.
We dont have CPU affinitised and SQL Server is the only app on the box.
Are there some relevant perfmon counters I can check
tks
--
-- cranfield, DBAThis is not unusual. You have a SQL process that is not parallelizable.
Probably something with derived columns, cursors, or intense calculations.
Those are the "usual suspects" when it comes to single-threaded apps. SQL
runs processes across multiple processors where possible, but it is not
always possuble.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
> Hello
> I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
> running just fine but only one out of the 8 CPUs is running at 90%+
> constantly. So avg is OK bit our alerting doesn't understand this.
> Any way of determining what is running on that 1 CPU? This does not look
> normal.
> We dont have CPU affinitised and SQL Server is the only app on the box.
> Are there some relevant perfmon counters I can check
> tks
> --
> -- cranfield, DBA|||Thanks for the reply.
We have complete mirrored/parallel systems and on ServerB (which is
identical to the ServerA in question) we see similar CPU profile where 1 of
the CPUs spikes. Its not as pronounced as ServerA and load seems to be muc
h
more evenly shared across the CPUs.
ServerA - CPU 0,1,2,3,4,5,7 - avg 5-10%; CPU6 - avg 90%
ServerB - CPU 0,1,2,3,4,5,7 - avg 10-30%; CPU6 - avg 60%
* exact same hardware
* exact same load (mostly continuous, batched, 1024 row bulk inserts)
*
I wonder what is causing the difference. Obviously I would prefer them to
use all CPUs.
-- cranfield, DBA
"Geoff N. Hiten" wrote:

> This is not unusual. You have a SQL process that is not parallelizable.
> Probably something with derived columns, cursors, or intense calculations.
> Those are the "usual suspects" when it comes to single-threaded apps. SQL
> runs processes across multiple processors where possible, but it is not
> always possuble.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
>|||If it's always the same CPU I'd be a bit suspicious. Check for an affinity
mask. What Sql Server product/licence are you using?
We get worse performance with parallel query. Although we have maxdup at 1
we still use all 8 cpus.
Regards
Paul Cahill
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:563A3C37-DDD5-4816-8898-C74966E33C00@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply.
> We have complete mirrored/parallel systems and on ServerB (which is
> identical to the ServerA in question) we see similar CPU profile where 1
> of
> the CPUs spikes. Its not as pronounced as ServerA and load seems to be
> much
> more evenly shared across the CPUs.
> ServerA - CPU 0,1,2,3,4,5,7 - avg 5-10%; CPU6 - avg 90%
> ServerB - CPU 0,1,2,3,4,5,7 - avg 10-30%; CPU6 - avg 60%
> * exact same hardware
> * exact same load (mostly continuous, batched, 1024 row bulk inserts)
> *
> I wonder what is causing the difference. Obviously I would prefer them to
> use all CPUs.
> --
> -- cranfield, DBA
>
> "Geoff N. Hiten" wrote:
>|||What version of SQL Server and Service pack? The way in which the
connections are tied to schedulers is different in both with 2000 having
more issues. How many concurrent connections do you have and what processors
are most of them on?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
> Hello
> I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
> running just fine but only one out of the 8 CPUs is running at 90%+
> constantly. So avg is OK bit our alerting doesn't understand this.
> Any way of determining what is running on that 1 CPU? This does not look
> normal.
> We dont have CPU affinitised and SQL Server is the only app on the box.
> Are there some relevant perfmon counters I can check
> tks
> --
> -- cranfield, DBA|||morning!
Both servers on SP2 + hotfix:
SQL Server 2005 - 9.00.3152.00 (Intel X86) Enterprise Edition on Windows NT
5.2 (Build 3790: Service Pack 2)
* sp_who2 shows 69 SPIDs (28 active)
How do I know which processers my connections are on?
tks
--
-- cranfield, DBA
"Andrew J. Kelly" wrote:

> What version of SQL Server and Service pack? The way in which the
> connections are tied to schedulers is different in both with 2000 having
> more issues. How many concurrent connections do you have and what processo
rs
> are most of them on?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
>

only 1 out of 8 CPU maxed out

Hello
I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
running just fine but only one out of the 8 CPUs is running at 90%+
constantly. So avg is OK bit our alerting doesn't understand this.
Any way of determining what is running on that 1 CPU? This does not look
normal.
We dont have CPU affinitised and SQL Server is the only app on the box.
Are there some relevant perfmon counters I can check
tks
--
-- cranfield, DBAThis is not unusual. You have a SQL process that is not parallelizable.
Probably something with derived columns, cursors, or intense calculations.
Those are the "usual suspects" when it comes to single-threaded apps. SQL
runs processes across multiple processors where possible, but it is not
always possuble.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
> Hello
> I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
> running just fine but only one out of the 8 CPUs is running at 90%+
> constantly. So avg is OK bit our alerting doesn't understand this.
> Any way of determining what is running on that 1 CPU? This does not look
> normal.
> We dont have CPU affinitised and SQL Server is the only app on the box.
> Are there some relevant perfmon counters I can check
> tks
> --
> -- cranfield, DBA|||Thanks for the reply.
We have complete mirrored/parallel systems and on ServerB (which is
identical to the ServerA in question) we see similar CPU profile where 1 of
the CPUs spikes. Its not as pronounced as ServerA and load seems to be much
more evenly shared across the CPUs.
ServerA - CPU 0,1,2,3,4,5,7 - avg 5-10%; CPU6 - avg 90%
ServerB - CPU 0,1,2,3,4,5,7 - avg 10-30%; CPU6 - avg 60%
* exact same hardware
* exact same load (mostly continuous, batched, 1024 row bulk inserts)
*
I wonder what is causing the difference. Obviously I would prefer them to
use all CPUs.
--
-- cranfield, DBA
"Geoff N. Hiten" wrote:
> This is not unusual. You have a SQL process that is not parallelizable.
> Probably something with derived columns, cursors, or intense calculations.
> Those are the "usual suspects" when it comes to single-threaded apps. SQL
> runs processes across multiple processors where possible, but it is not
> always possuble.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
> > Hello
> >
> > I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
> > running just fine but only one out of the 8 CPUs is running at 90%+
> > constantly. So avg is OK bit our alerting doesn't understand this.
> >
> > Any way of determining what is running on that 1 CPU? This does not look
> > normal.
> >
> > We dont have CPU affinitised and SQL Server is the only app on the box.
> >
> > Are there some relevant perfmon counters I can check
> >
> > tks
> > --
> > -- cranfield, DBA
>|||If it's always the same CPU I'd be a bit suspicious. Check for an affinity
mask. What Sql Server product/licence are you using?
We get worse performance with parallel query. Although we have maxdup at 1
we still use all 8 cpus.
Regards
Paul Cahill
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:563A3C37-DDD5-4816-8898-C74966E33C00@.microsoft.com...
> Thanks for the reply.
> We have complete mirrored/parallel systems and on ServerB (which is
> identical to the ServerA in question) we see similar CPU profile where 1
> of
> the CPUs spikes. Its not as pronounced as ServerA and load seems to be
> much
> more evenly shared across the CPUs.
> ServerA - CPU 0,1,2,3,4,5,7 - avg 5-10%; CPU6 - avg 90%
> ServerB - CPU 0,1,2,3,4,5,7 - avg 10-30%; CPU6 - avg 60%
> * exact same hardware
> * exact same load (mostly continuous, batched, 1024 row bulk inserts)
> *
> I wonder what is causing the difference. Obviously I would prefer them to
> use all CPUs.
> --
> -- cranfield, DBA
>
> "Geoff N. Hiten" wrote:
>> This is not unusual. You have a SQL process that is not parallelizable.
>> Probably something with derived columns, cursors, or intense
>> calculations.
>> Those are the "usual suspects" when it comes to single-threaded apps.
>> SQL
>> runs processes across multiple processors where possible, but it is not
>> always possuble.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
>> > Hello
>> >
>> > I'm getting CPU alerts from one of our SQL servers. Problem is that SQL
>> > is
>> > running just fine but only one out of the 8 CPUs is running at 90%+
>> > constantly. So avg is OK bit our alerting doesn't understand this.
>> >
>> > Any way of determining what is running on that 1 CPU? This does not
>> > look
>> > normal.
>> >
>> > We dont have CPU affinitised and SQL Server is the only app on the box.
>> >
>> > Are there some relevant perfmon counters I can check
>> >
>> > tks
>> > --
>> > -- cranfield, DBA
>>|||What version of SQL Server and Service pack? The way in which the
connections are tied to schedulers is different in both with 2000 having
more issues. How many concurrent connections do you have and what processors
are most of them on?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
> Hello
> I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
> running just fine but only one out of the 8 CPUs is running at 90%+
> constantly. So avg is OK bit our alerting doesn't understand this.
> Any way of determining what is running on that 1 CPU? This does not look
> normal.
> We dont have CPU affinitised and SQL Server is the only app on the box.
> Are there some relevant perfmon counters I can check
> tks
> --
> -- cranfield, DBA|||morning!
Both servers on SP2 + hotfix:
SQL Server 2005 - 9.00.3152.00 (Intel X86) Enterprise Edition on Windows NT
5.2 (Build 3790: Service Pack 2)
* sp_who2 shows 69 SPIDs (28 active)
How do I know which processers my connections are on?
tks
--
-- cranfield, DBA
"Andrew J. Kelly" wrote:
> What version of SQL Server and Service pack? The way in which the
> connections are tied to schedulers is different in both with 2000 having
> more issues. How many concurrent connections do you have and what processors
> are most of them on?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:DA994330-99BA-4133-9739-02775C61ACEE@.microsoft.com...
> > Hello
> >
> > I'm getting CPU alerts from one of our SQL servers. Problem is that SQL is
> > running just fine but only one out of the 8 CPUs is running at 90%+
> > constantly. So avg is OK bit our alerting doesn't understand this.
> >
> > Any way of determining what is running on that 1 CPU? This does not look
> > normal.
> >
> > We dont have CPU affinitised and SQL Server is the only app on the box.
> >
> > Are there some relevant perfmon counters I can check
> >
> > tks
> > --
> > -- cranfield, DBA
>