Job monitor for SQLServer

Any recommendations for software that provides a nice end-user
interface to records of jobs run in SQLServer, hopefully with a
real-time updating as jobs run as well?
Thanks.
JoshHi
You may want to look at http://www.idera.com/Products/SQLschedule/
John
"jxstern" wrote:
> Any recommendations for software that provides a nice end-user
> interface to records of jobs run in SQLServer, hopefully with a
> real-time updating as jobs run as well?
> Thanks.
> Josh
>

Job monitor for SQLServer

Any recommendations for software that provides a nice end-user
interface to records of jobs run in SQLServer, hopefully with a
real-time updating as jobs run as well?
Thanks.
JoshHi
You may want to look at http://www.idera.com/Products/SQLschedule/
John
"jxstern" wrote:

> Any recommendations for software that provides a nice end-user
> interface to records of jobs run in SQLServer, hopefully with a
> real-time updating as jobs run as well?
> Thanks.
> Josh
>

Job failed with owner ()

Recently I've been seeing the following error when SQL
Server Agent jobs are run. But this doesn't always
happen, sometime the jobs run fine.
The job failed. The owner () of job xxxxxxxxxxx does not
have server access.
Any thoughts?
Is the job owned by a group that is in a Domain Local Group?
If so, you'll need this update.
825042 FIX: SQL Server Jobs That Are Owned by Non-sysadmin Users May Not
Start
http://support.microsoft.com/?id=825042
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Job failed with owner ()

Recently I've been seeing the following error when SQL
Server Agent jobs are run. But this doesn't always
happen, sometime the jobs run fine.
The job failed. The owner () of job xxxxxxxxxxx does not
have server access.
Any thoughts?Is the job owned by a group that is in a Domain Local Group?
If so, you'll need this update.
825042 FIX: SQL Server Jobs That Are Owned by Non-sysadmin Users May Not
Start
http://support.microsoft.com/?id=825042
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

job failed after domain IP address changed

I've create a maintenance plan using the SQLserver Wizard.

The jobs work correctly until we change the IP address of the server with the following trace :

The job failed. Unable to determine if the owner (DOMAIN\Login) of job Transaction Log Backup Job for DB Maintenance Plan 'BASE' has server access (reason: Could not obtain information about Windows NT group/user 'DOMAIN\login'. [SQLSTATE 42000] (Error 8198)).

Can somebpody explain me what occured ?Howdy

I have seen this a lot on SQL 7 and it seems the NT authentication/ SQL interface neve rreally worked properly.

Easiest way ( and usually the best way of going anyway ) is get sa to own all jobs, then unless its a very secure server, all will run fine and if you change the IP adderess of the server, as sa is local, it wont make any difference.

Cheers

SG.

JDBC with SQL Express

The java code :

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
"instance=SQLEXPRESS;databaseName=UPM;integratedSecurity=true;";
Connection con = DriverManager.getConnection(connectionUrl);

returns "connection refused" no matter what I do.

I've used the surface config tool to enable TCP, I've added sqlsrvr.exe to the firewall exceptions, I've tried a dozen or so variations on the connection string.

I can connect to the database using the Management Studio.

Any ideas what I'm missing ?

Thanks.

As you are using a trusted connection can your verify that you are using the correct user in both systems.

|||

I'm running into the same problem.

No problem connecting through VWD or SQL Management Studio...

The code:

String url =
"jdbc:sqlserver://localhost;" +
//"databaseName=TV;" +
"portNumber=1433;" +
"instanceName=./SQLEXPRESS;" +
"loginTimeout=0;" +
//"userName=Pierre;" +
//"serverName=PG" +
"integratedSecurity=true;";

con = DriverManager.getConnection( url );

Same bad result with all commented parameters.

The message:

The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect.

|||
This may be because by default MS SQL Server Express is configured to use dynamic TCP/IP ports, like named instances.

Go into the SQL SERVER CONFIGURATION MANAGER
open SQL SERVER 2005 Network Configuration
open Protocols for SQLEXPRESS
open TCP/IP properties
on the IP ADDRESSES tab, check at the bottom if "TCP Dynamic Ports" has a value.

if so, clear the value and leave that field blank. Then change "TCP Port" to 1433 or whatever port you decide.

JDBC slow in the morning?!

Hello,
I have a strange problem...
I manage a web application on Tomcat, Struts, SQLServer with the last
jdbc driver from Microsoft.
So, each morning one stored proc is very slow (around 20 sec to
execute) and around 10:00 AM, the stored proc accelerate (around 500
ms)!!
This stored proc is executing one query, and if necessary, insert
records for caching in a cache table.
Here is my code...
/*******************************************************************
* DocumentsOfEDMChanelForUser -
EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
TRUNCATE TABLE cache_EDMRights
********************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DocumentsOfEDMChanelForUser]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DocumentsOfEDMChanelForUser
@.TopCount int
,@.LanguageOID int
,@.SecurityUserOID int
,@.ChanelOID int
AS
SET NOCOUNT ON
SELECT DISTINCT
Document.DOCUMENT_PK AS OID,
dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
DocumentName,
dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
Description,
Document.LastModify AS CreationDate,
dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
FolderPath,
dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
@.SecurityUserOID) AS IsReaded,
dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
0, 1) AS Rights
INTO
#ResultSet
FROM
Document Document INNER JOIN
EDMChanel EDMChanel ON Document.EDMChanel = EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
CodeBasis CodeBasis ON EDMChanel.PeriodType = CodeBasis.CODEBASIS_PK
WHERE
Document.EDMChanel = @.ChanelOID AND
Document.Active = 1 AND
CASE
WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Month' THEN
DATEADD(month, EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
EDMChanel.Period, Document.LastModify)
END >= getdate()
--ORDER BY
-- CreationDate DESC
IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
BEGIN
-- LEFT JOIN with cache because there are index violation with
insert
INSERT INTO cache_EDMRights
SELECT
0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
FROM
#ResultSet R LEFT OUTER JOIN
cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser = @.SecurityUserOID AND C.OID = R.OID
WHERE
C.OID IS NULL AND
(R.Rights & 65536) > 0
UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
65536) > 0
END
DECLARE @.SQL varchar(5000)
SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
EXEC(@.SQL)
DROP TABLE #ResultSet
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I'm not understanding?!
Anyone has idea?
Thanks for help.Hi
How do you know that it's JDBC issue?
Have you ran SQL Server Profiler to track down what is going on during the
execution?
Also ,I'd create a temporary table by CREATE TABLE #Temp and not by SELECT *
INTO ...
Another point, it might be a bad idea to use UDF with a large set data,
because it performs row-by-row process similar how cursors work.
"crabouif" <pascal_fluck@.hotmail.com> wrote in message
news:109014db.0411240054.4fc04476@.posting.google.com...
> Hello,
> I have a strange problem...
> I manage a web application on Tomcat, Struts, SQLServer with the last
> jdbc driver from Microsoft.
> So, each morning one stored proc is very slow (around 20 sec to
> execute) and around 10:00 AM, the stored proc accelerate (around 500
> ms)!!
> This stored proc is executing one query, and if necessary, insert
> records for caching in a cache table.
> Here is my code...
> /*******************************************************************
> * DocumentsOfEDMChanelForUser -
> EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> TRUNCATE TABLE cache_EDMRights
> ********************************************************************/
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
> AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
> @.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> 0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel => EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType => CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
> insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser => @.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> 65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I'm not understanding?!
> Anyone has idea?
> Thanks for help.|||As Uri already indicated, you may not want to assume it's
just a JDBC issue. In addition to profiler, you would want
to check the activity on the server (using PerfMon) as well
as the activity in SQL Server. When something runs slow at
particular times, it's often due to other activities on the
server or in SQL Server that are using a lot of resources.
-Sue
On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Hello,
>I have a strange problem...
>I manage a web application on Tomcat, Struts, SQLServer with the last
>jdbc driver from Microsoft.
>So, each morning one stored proc is very slow (around 20 sec to
>execute) and around 10:00 AM, the stored proc accelerate (around 500
>ms)!!
>This stored proc is executing one query, and if necessary, insert
>records for caching in a cache table.
>Here is my code...
>/*******************************************************************
>* DocumentsOfEDMChanelForUser -
>EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>TRUNCATE TABLE cache_EDMRights
>********************************************************************/
>if exists (select * from dbo.sysobjects where id =>object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>
>CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
>AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
>@.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =>EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =>CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
>insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =>@.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>I'm not understanding?!
>Anyone has idea?
>Thanks for help.|||Well, I endly solve my problem.
So I check that I had an index is breaked. And in 10:00 AM I had an
agent who reindex it.
Thanks for your idees, I solve that problem with profiler...
But what I dont explain is why with JDBC (respectively my application
and Aqua Data Studio) it was slow, and with MS Query Analyser it was
quick...
Thanks one more for you help.
Pascal
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>...
> As Uri already indicated, you may not want to assume it's
> just a JDBC issue. In addition to profiler, you would want
> to check the activity on the server (using PerfMon) as well
> as the activity in SQL Server. When something runs slow at
> particular times, it's often due to other activities on the
> server or in SQL Server that are using a lot of resources.
> -Sue
> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
> (crabouif) wrote:
> >Hello,
> >
> >I have a strange problem...
> >
> >I manage a web application on Tomcat, Struts, SQLServer with the last
> >jdbc driver from Microsoft.
> >
> >So, each morning one stored proc is very slow (around 20 sec to
> >execute) and around 10:00 AM, the stored proc accelerate (around 500
> >ms)!!
> >
> >This stored proc is executing one query, and if necessary, insert
> >records for caching in a cache table.
> >
> >Here is my code...
> >
> >/*******************************************************************
> >* DocumentsOfEDMChanelForUser -
> >EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> >TRUNCATE TABLE cache_EDMRights
> >********************************************************************/
> >if exists (select * from dbo.sysobjects where id => >object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> >OBJECTPROPERTY(id, N'IsProcedure') = 1)
> >drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> >GO
> >
> >SET QUOTED_IDENTIFIER ON
> >GO
> >SET ANSI_NULLS ON
> >GO
> >
> >
> >CREATE PROCEDURE DocumentsOfEDMChanelForUser
> > @.TopCount int
> > ,@.LanguageOID int
> > ,@.SecurityUserOID int
> > ,@.ChanelOID int
> >AS
> > SET NOCOUNT ON
> >
> > SELECT DISTINCT
> > Document.DOCUMENT_PK AS OID,
> > dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> >DocumentName,
> > dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> >Description,
> > Document.LastModify AS CreationDate,
> > dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> >FolderPath,
> > dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
> >@.SecurityUserOID) AS IsReaded,
> > dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> >0, 1) AS Rights
> > INTO
> > #ResultSet
> > FROM
> > Document Document INNER JOIN
> > EDMChanel EDMChanel ON Document.EDMChanel => >EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> > CodeBasis CodeBasis ON EDMChanel.PeriodType => >CodeBasis.CODEBASIS_PK
> > WHERE
> > Document.EDMChanel = @.ChanelOID AND
> > Document.Active = 1 AND
> > CASE
> > WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> >EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> >EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> >DATEADD(month, EDMChanel.Period, Document.LastModify)
> > WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> >EDMChanel.Period, Document.LastModify)
> > END >= getdate()
> > --ORDER BY
> > -- CreationDate DESC
> >
> > IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> > BEGIN
> > -- LEFT JOIN with cache because there are index violation with
> >insert
> > INSERT INTO cache_EDMRights
> > SELECT
> > 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> > FROM
> > #ResultSet R LEFT OUTER JOIN
> > cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser => >@.SecurityUserOID AND C.OID = R.OID
> > WHERE
> > C.OID IS NULL AND
> > (R.Rights & 65536) > 0
> > UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> >65536) > 0
> > END
> >
> > DECLARE @.SQL varchar(5000)
> > SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> >FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> > EXEC(@.SQL)
> > DROP TABLE #ResultSet
> >GO
> >SET QUOTED_IDENTIFIER OFF
> >GO
> >SET ANSI_NULLS ON
> >GO
> >
> >I'm not understanding?!
> >
> >Anyone has idea?
> >
> >Thanks for help.|||One thing is that you could have been seeing the effects of
caching and in combination with calling the stored procedure
differently in the two scenarios. You can monitor the
caching - cache hits, cache misses, etc. using Profiler.
-Sue
On 25 Nov 2004 22:58:40 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
>Well, I endly solve my problem.
>So I check that I had an index is breaked. And in 10:00 AM I had an
>agent who reindex it.
>Thanks for your idees, I solve that problem with profiler...
>But what I dont explain is why with JDBC (respectively my application
>and Aqua Data Studio) it was slow, and with MS Query Analyser it was
>quick...
>Thanks one more for you help.
> Pascal
>
>Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>...
>> As Uri already indicated, you may not want to assume it's
>> just a JDBC issue. In addition to profiler, you would want
>> to check the activity on the server (using PerfMon) as well
>> as the activity in SQL Server. When something runs slow at
>> particular times, it's often due to other activities on the
>> server or in SQL Server that are using a lot of resources.
>> -Sue
>> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
>> (crabouif) wrote:
>> >Hello,
>> >
>> >I have a strange problem...
>> >
>> >I manage a web application on Tomcat, Struts, SQLServer with the last
>> >jdbc driver from Microsoft.
>> >
>> >So, each morning one stored proc is very slow (around 20 sec to
>> >execute) and around 10:00 AM, the stored proc accelerate (around 500
>> >ms)!!
>> >
>> >This stored proc is executing one query, and if necessary, insert
>> >records for caching in a cache table.
>> >
>> >Here is my code...
>> >
>> >/*******************************************************************
>> >* DocumentsOfEDMChanelForUser -
>> >EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>> >TRUNCATE TABLE cache_EDMRights
>> >********************************************************************/
>> >if exists (select * from dbo.sysobjects where id =>> >object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>> >OBJECTPROPERTY(id, N'IsProcedure') = 1)
>> >drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>> >GO
>> >
>> >SET QUOTED_IDENTIFIER ON
>> >GO
>> >SET ANSI_NULLS ON
>> >GO
>> >
>> >
>> >CREATE PROCEDURE DocumentsOfEDMChanelForUser
>> > @.TopCount int
>> > ,@.LanguageOID int
>> > ,@.SecurityUserOID int
>> > ,@.ChanelOID int
>> >AS
>> > SET NOCOUNT ON
>> >
>> > SELECT DISTINCT
>> > Document.DOCUMENT_PK AS OID,
>> > dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>> >DocumentName,
>> > dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>> >Description,
>> > Document.LastModify AS CreationDate,
>> > dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>> >FolderPath,
>> > dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
>> >@.SecurityUserOID) AS IsReaded,
>> > dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>> >0, 1) AS Rights
>> > INTO
>> > #ResultSet
>> > FROM
>> > Document Document INNER JOIN
>> > EDMChanel EDMChanel ON Document.EDMChanel =>> >EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
>> > CodeBasis CodeBasis ON EDMChanel.PeriodType =>> >CodeBasis.CODEBASIS_PK
>> > WHERE
>> > Document.EDMChanel = @.ChanelOID AND
>> > Document.Active = 1 AND
>> > CASE
>> > WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>> >EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>> >EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>> >DATEADD(month, EDMChanel.Period, Document.LastModify)
>> > WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>> >EDMChanel.Period, Document.LastModify)
>> > END >= getdate()
>> > --ORDER BY
>> > -- CreationDate DESC
>> >
>> > IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
>> > BEGIN
>> > -- LEFT JOIN with cache because there are index violation with
>> >insert
>> > INSERT INTO cache_EDMRights
>> > SELECT
>> > 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
>> > FROM
>> > #ResultSet R LEFT OUTER JOIN
>> > cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =>> >@.SecurityUserOID AND C.OID = R.OID
>> > WHERE
>> > C.OID IS NULL AND
>> > (R.Rights & 65536) > 0
>> > UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>> >65536) > 0
>> > END
>> >
>> > DECLARE @.SQL varchar(5000)
>> > SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>> >FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
>> > EXEC(@.SQL)
>> > DROP TABLE #ResultSet
>> >GO
>> >SET QUOTED_IDENTIFIER OFF
>> >GO
>> >SET ANSI_NULLS ON
>> >GO
>> >
>> >I'm not understanding?!
>> >
>> >Anyone has idea?
>> >
>> >Thanks for help.

JDBC slow in the morning?!

Hello,
I have a strange problem...
I manage a web application on Tomcat, Struts, SQLServer with the last
jdbc driver from Microsoft.
So, each morning one stored proc is very slow (around 20 sec to
execute) and around 10:00 AM, the stored proc accelerate (around 500
ms)!!
This stored proc is executing one query, and if necessary, insert
records for caching in a cache table.
Here is my code...
/************************************************** *****************
* DocumentsOfEDMChanelForUser -
EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
TRUNCATE TABLE cache_EDMRights
************************************************** ******************/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DocumentsOfEDMChanelForUser]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DocumentsOfEDMChanelForUser
@.TopCount int
,@.LanguageOID int
,@.SecurityUserOID int
,@.ChanelOID int
AS
SET NOCOUNT ON
SELECT DISTINCT
Document.DOCUMENT_PK AS OID,
dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
DocumentName,
dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
Description,
Document.LastModify AS CreationDate,
dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
FolderPath,
dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK ,
@.SecurityUserOID) AS IsReaded,
dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
0, 1) AS Rights
INTO
#ResultSet
FROM
Document Document INNER JOIN
EDMChanel EDMChanel ON Document.EDMChanel =
EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
CodeBasis CodeBasis ON EDMChanel.PeriodType =
CodeBasis.CODEBASIS_PK
WHERE
Document.EDMChanel = @.ChanelOID AND
Document.Active = 1 AND
CASE
WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Month' THEN
DATEADD(month, EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
EDMChanel.Period, Document.LastModify)
END >= getdate()
--ORDER BY
-- CreationDate DESC
IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
BEGIN
-- LEFT JOIN with cache because there are index violation with
insert
INSERT INTO cache_EDMRights
SELECT
0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
FROM
#ResultSet R LEFT OUTER JOIN
cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
@.SecurityUserOID AND C.OID = R.OID
WHERE
C.OID IS NULL AND
(R.Rights & 65536) > 0
UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
65536) > 0
END
DECLARE @.SQL varchar(5000)
SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
EXEC(@.SQL)
DROP TABLE #ResultSet
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I'm not understanding?!
Anyone has idea?
Thanks for help.
Hi
How do you know that it's JDBC issue?
Have you ran SQL Server Profiler to track down what is going on during the
execution?
Also ,I'd create a temporary table by CREATE TABLE #Temp and not by SELECT *
INTO ...
Another point, it might be a bad idea to use UDF with a large set data,
because it performs row-by-row process similar how cursors work.
"crabouif" <pascal_fluck@.hotmail.com> wrote in message
news:109014db.0411240054.4fc04476@.posting.google.c om...
> Hello,
> I have a strange problem...
> I manage a web application on Tomcat, Struts, SQLServer with the last
> jdbc driver from Microsoft.
> So, each morning one stored proc is very slow (around 20 sec to
> execute) and around 10:00 AM, the stored proc accelerate (around 500
> ms)!!
> This stored proc is executing one query, and if necessary, insert
> records for caching in a cache table.
> Here is my code...
> /************************************************** *****************
> * DocumentsOfEDMChanelForUser -
> EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> TRUNCATE TABLE cache_EDMRights
> ************************************************** ******************/
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
> AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK ,
> @.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> 0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =
> EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =
> CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
> insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
> @.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> 65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I'm not understanding?!
> Anyone has idea?
> Thanks for help.
|||As Uri already indicated, you may not want to assume it's
just a JDBC issue. In addition to profiler, you would want
to check the activity on the server (using PerfMon) as well
as the activity in SQL Server. When something runs slow at
particular times, it's often due to other activities on the
server or in SQL Server that are using a lot of resources.
-Sue
On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:

>Hello,
>I have a strange problem...
>I manage a web application on Tomcat, Struts, SQLServer with the last
>jdbc driver from Microsoft.
>So, each morning one stored proc is very slow (around 20 sec to
>execute) and around 10:00 AM, the stored proc accelerate (around 500
>ms)!!
>This stored proc is executing one query, and if necessary, insert
>records for caching in a cache table.
>Here is my code...
>/************************************************** *****************
>* DocumentsOfEDMChanelForUser -
>EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>TRUNCATE TABLE cache_EDMRights
>************************************************* *******************/
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>
>CREATE PROCEDURE DocumentsOfEDMChanelForUser
>@.TopCount int
>,@.LanguageOID int
>,@.SecurityUserOID int
>,@.ChanelOID int
>AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK ,
>@.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =
>EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =
>CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
>insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
>@.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>I'm not understanding?!
>Anyone has idea?
>Thanks for help.
|||Well, I endly solve my problem.
So I check that I had an index is breaked. And in 10:00 AM I had an
agent who reindex it.
Thanks for your idees, I solve that problem with profiler...
But what I dont explain is why with JDBC (respectively my application
and Aqua Data Studio) it was slow, and with MS Query Analyser it was
quick...
Thanks one more for you help.
Pascal
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>. ..[vbcol=seagreen]
> As Uri already indicated, you may not want to assume it's
> just a JDBC issue. In addition to profiler, you would want
> to check the activity on the server (using PerfMon) as well
> as the activity in SQL Server. When something runs slow at
> particular times, it's often due to other activities on the
> server or in SQL Server that are using a lot of resources.
> -Sue
> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
> (crabouif) wrote:
|||One thing is that you could have been seeing the effects of
caching and in combination with calling the stored procedure
differently in the two scenarios. You can monitor the
caching - cache hits, cache misses, etc. using Profiler.
-Sue
On 25 Nov 2004 22:58:40 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
[vbcol=seagreen]
>Well, I endly solve my problem.
>So I check that I had an index is breaked. And in 10:00 AM I had an
>agent who reindex it.
>Thanks for your idees, I solve that problem with profiler...
>But what I dont explain is why with JDBC (respectively my application
>and Aqua Data Studio) it was slow, and with MS Query Analyser it was
>quick...
>Thanks one more for you help.
> Pascal
>
>Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q18@.4ax.com>. ..

JDBC slow in the morning?!

Hello,
I have a strange problem...
I manage a web application on Tomcat, Struts, SQLServer with the last
jdbc driver from Microsoft.
So, each morning one stored proc is very slow (around 20 sec to
execute) and around 10:00 AM, the stored proc accelerate (around 500
ms)!!
This stored proc is executing one query, and if necessary, insert
records for caching in a cache table.
Here is my code...
/ ****************************************
***************************
* DocumentsOfEDMChanelForUser -
EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
TRUNCATE TABLE cache_EDMRights
****************************************
****************************/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DocumentsOfEDMChanelForUser]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DocumentsOfEDMChanelForUser
@.TopCount int
,@.LanguageOID int
,@.SecurityUserOID int
,@.ChanelOID int
AS
SET NOCOUNT ON
SELECT DISTINCT
Document.DOCUMENT_PK AS OID,
dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
DocumentName,
dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
Description,
Document.LastModify AS CreationDate,
dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
FolderPath,
dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
@.SecurityUserOID) AS IsReaded,
dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
0, 1) AS Rights
INTO
#ResultSet
FROM
Document Document INNER JOIN
EDMChanel EDMChanel ON Document.EDMChanel =
EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
CodeBasis CodeBasis ON EDMChanel.PeriodType =
CodeBasis.CODEBASIS_PK
WHERE
Document.EDMChanel = @.ChanelOID AND
Document.Active = 1 AND
CASE
WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Month' THEN
DATEADD(month, EDMChanel.Period, Document.LastModify)
WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
EDMChanel.Period, Document.LastModify)
END >= getdate()
--ORDER BY
-- CreationDate DESC
IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
BEGIN
-- LEFT JOIN with cache because there are index violation with
insert
INSERT INTO cache_EDMRights
SELECT
0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
FROM
#ResultSet R LEFT OUTER JOIN
cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
@.SecurityUserOID AND C.OID = R.OID
WHERE
C.OID IS NULL AND
(R.Rights & 65536) > 0
UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
65536) > 0
END
DECLARE @.SQL varchar(5000)
SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
EXEC(@.SQL)
DROP TABLE #ResultSet
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I'm not understanding?!
Anyone has idea?
Thanks for help.Hi
How do you know that it's JDBC issue?
Have you ran SQL Server Profiler to track down what is going on during the
execution?
Also ,I'd create a temporary table by CREATE TABLE #Temp and not by SELECT *
INTO ...
Another point, it might be a bad idea to use UDF with a large set data,
because it performs row-by-row process similar how cursors work.
"crabouif" <pascal_fluck@.hotmail.com> wrote in message
news:109014db.0411240054.4fc04476@.posting.google.com...
> Hello,
> I have a strange problem...
> I manage a web application on Tomcat, Struts, SQLServer with the last
> jdbc driver from Microsoft.
> So, each morning one stored proc is very slow (around 20 sec to
> execute) and around 10:00 AM, the stored proc accelerate (around 500
> ms)!!
> This stored proc is executing one query, and if necessary, insert
> records for caching in a cache table.
> Here is my code...
> / ****************************************
***************************
> * DocumentsOfEDMChanelForUser -
> EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
> TRUNCATE TABLE cache_EDMRights
> ****************************************
****************************/
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
> OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[DocumentsOfEDMChanelForUser]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
> AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
> DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
> Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
> FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
> @.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
> 0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =
> EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =
> CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
> EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
> DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
> EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
> insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
> @.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
> 65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
> FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I'm not understanding?!
> Anyone has idea?
> Thanks for help.|||As Uri already indicated, you may not want to assume it's
just a JDBC issue. In addition to profiler, you would want
to check the activity on the server (using PerfMon) as well
as the activity in SQL Server. When something runs slow at
particular times, it's often due to other activities on the
server or in SQL Server that are using a lot of resources.
-Sue
On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:

>Hello,
>I have a strange problem...
>I manage a web application on Tomcat, Struts, SQLServer with the last
>jdbc driver from Microsoft.
>So, each morning one stored proc is very slow (around 20 sec to
>execute) and around 10:00 AM, the stored proc accelerate (around 500
>ms)!!
>This stored proc is executing one query, and if necessary, insert
>records for caching in a cache table.
>Here is my code...
>/ ****************************************
***************************
>* DocumentsOfEDMChanelForUser -
>EXEC DocumentsOfEDMChanelForUser 10, 4, 576, 3
>TRUNCATE TABLE cache_EDMRights
> ****************************************
****************************/
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[DocumentsOfEDMChanelForUser]') and
>OBJECTPROPERTY(id, N'IsProcedure') = 1)
>drop procedure [dbo].[DocumentsOfEDMChanelForUser]
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>
>CREATE PROCEDURE DocumentsOfEDMChanelForUser
> @.TopCount int
> ,@.LanguageOID int
> ,@.SecurityUserOID int
> ,@.ChanelOID int
>AS
> SET NOCOUNT ON
> SELECT DISTINCT
> Document.DOCUMENT_PK AS OID,
> dbo.getLexiconText(Document.DisplayName, @.LanguageOID, '') AS
>DocumentName,
> dbo.getLexiconText(Document.Description, @.LanguageOID, '') AS
>Description,
> Document.LastModify AS CreationDate,
> dbo.getFolderPath(Document.Folder, @.LanguageOID, '/') AS
>FolderPath,
> dbo.getIsDocumentReadedByUser(Document.DOCUMENT_PK,
>@.SecurityUserOID) AS IsReaded,
> dbo.getDocumentRights(@.SecurityUserOID, Document.DOCUMENT_PK,
>0, 1) AS Rights
> INTO
> #ResultSet
> FROM
> Document Document INNER JOIN
> EDMChanel EDMChanel ON Document.EDMChanel =
>EDMChanel.EDMCHANEL_CHANEL_IPK INNER JOIN
> CodeBasis CodeBasis ON EDMChanel.PeriodType =
>CodeBasis.CODEBASIS_PK
> WHERE
> Document.EDMChanel = @.ChanelOID AND
> Document.Active = 1 AND
> CASE
> WHEN CodeBasis.Code = 'PeriodType.Day' THEN DATEADD(day,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Week' THEN DATEADD(week,
>EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Month' THEN
>DATEADD(month, EDMChanel.Period, Document.LastModify)
> WHEN CodeBasis.Code = 'PeriodType.Year' THEN DATEADD(year,
>EDMChanel.Period, Document.LastModify)
> END >= getdate()
> --ORDER BY
> -- CreationDate DESC
> IF EXISTS(SELECT 1 FROM #ResultSet WHERE (Rights & 65536) > 0)
> BEGIN
> -- LEFT JOIN with cache because there are index violation with
>insert
> INSERT INTO cache_EDMRights
> SELECT
> 0, @.SecurityUserOID, R.OID, R.Rights ^ 65536
> FROM
> #ResultSet R LEFT OUTER JOIN
> cache_EDMRights C ON IsFolder = 0 AND C.SecurityUser =
>@.SecurityUserOID AND C.OID = R.OID
> WHERE
> C.OID IS NULL AND
> (R.Rights & 65536) > 0
> UPDATE #ResultSet SET Rights = Rights ^ 65536 WHERE (Rights &
>65536) > 0
> END
> DECLARE @.SQL varchar(5000)
> SET @.SQL = 'SELECT TOP ' + cast(@.TopCount as varchar(10)) + ' *
>FROM #ResultSet WHERE Rights > 0 ORDER BY CreationDate DESC'
> EXEC(@.SQL)
> DROP TABLE #ResultSet
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>I'm not understanding?!
>Anyone has idea?
>Thanks for help.|||Well, I endly solve my problem.
So I check that I had an index is breaked. And in 10:00 AM I had an
agent who reindex it.
Thanks for your idees, I solve that problem with profiler...
But what I dont explain is why with JDBC (respectively my application
and Aqua Data Studio) it was slow, and with MS Query Analyser it was
quick...
Thanks one more for you help.
Pascal
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m735s2mdt4eed5h23q
18@.4ax.com>...[vbcol=seagreen]
> As Uri already indicated, you may not want to assume it's
> just a JDBC issue. In addition to profiler, you would want
> to check the activity on the server (using PerfMon) as well
> as the activity in SQL Server. When something runs slow at
> particular times, it's often due to other activities on the
> server or in SQL Server that are using a lot of resources.
> -Sue
> On 24 Nov 2004 00:54:00 -0800, pascal_fluck@.hotmail.com
> (crabouif) wrote:
>|||One thing is that you could have been seeing the effects of
caching and in combination with calling the stored procedure
differently in the two scenarios. You can monitor the
caching - cache hits, cache misses, etc. using Profiler.
-Sue
On 25 Nov 2004 22:58:40 -0800, pascal_fluck@.hotmail.com
(crabouif) wrote:
[vbcol=seagreen]
>Well, I endly solve my problem.
>So I check that I had an index is breaked. And in 10:00 AM I had an
>agent who reindex it.
>Thanks for your idees, I solve that problem with profiler...
>But what I dont explain is why with JDBC (respectively my application
>and Aqua Data Studio) it was slow, and with MS Query Analyser it was
>quick...
>Thanks one more for you help.
> Pascal
>
>Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<sn99q0p7d6ii61m
735s2mdt4eed5h23q18@.4ax.com>...

JDBC sample code using named pipe and SQLServer 2005 driver?

Hi,

Does anybody have a working Java code sample that connects to an SQLServer 2005 database on a remote host, via the default named pipe, from a client using the SQLServer 2005 JDBC driver? Could you post it, or a pointer to it?

I've gotten java.sql DriverManager.getConnection() to work fine with TCP/IP connections before. But I'm a newbie with named pipes, and unclear on how the connection string/properties are different. I've tried to piece it together from multiple docs and threads, but haven't found sample code that quite fits my situation. I think a simple working example would best clarify the syntax.

The server is not using SQL Express. Most SQLServer configuration options are defaults; the named pipes protocol is enabled.

Thanks

Hi Dan,

The Microsoft SQL Server 2005 JDBC Driver does not support connections over named pipes. It only supports TCP/IP connections.

--David Olix

|||David,

Thanks for the info and your quick response.

I suggest mentioning this in the JDBC docs. I couldn't find it in my searching.

- Dan N.

JDBC Error Incorrect syntax near ''-''.

Hi I am using JDBC driver version "1.0.809.102". In the ms sqlserver 2000 database I am acessing there is a table named as "CDR_DATA_2007-09-12". When I run the following query "select * from CDR_DATA_2007-09-12" i get the following exception. Please help me out to solve this problem.

com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: Incorrect syntax near '-'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
at Test.mssqldb(Test.java:80)
at Test.main(Test.java:26)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:86)

Thanks,
Gavin

Hi Gavin,

Looks like SQL Server is thinking "CDR_DATA_2007-09-12" is a numeric operation of "CDR_DATA_2007" - 09 - 12.

Try escaping the table name with '[]'. So the SELECT statement will look like:

"select * from [CDR_DATA_2007-09-12]"

HTH,

Jimmy

|||Hi Jimmy,

Thanks it worked.

Cheers,
Gavin

JDBC Error Incorrect syntax near ''-''.

Hi I am using JDBC driver version "1.0.809.102". In the ms sqlserver 2000 database I am acessing there is a table named as "CDR_DATA_2007-09-12". When I run the following query "select * from CDR_DATA_2007-09-12" i get the following exception. Please help me out to solve this problem.

com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: Incorrect syntax near '-'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
at Test.mssqldb(Test.java:80)
at Test.main(Test.java:26)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:86)

Thanks,
Gavin

Hi Gavin,

Looks like SQL Server is thinking "CDR_DATA_2007-09-12" is a numeric operation of "CDR_DATA_2007" - 09 - 12.

Try escaping the table name with '[]'. So the SELECT statement will look like:

"select * from [CDR_DATA_2007-09-12]"

HTH,

Jimmy

|||Hi Jimmy,

Thanks it worked.

Cheers,
Gavin

JDBC Error Incorrect syntax near ''-''.

Hi I am using JDBC driver version "1.0.809.102". In the ms sqlserver 2000 database I am acessing there is a table named as "CDR_DATA_2007-09-12". When I run the following query "select * from CDR_DATA_2007-09-12" i get the following exception. Please help me out to solve this problem.

com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: Incorrect syntax near '-'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
at Test.mssqldb(Test.java:80)
at Test.main(Test.java:26)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:86)

Thanks,
Gavin

Hi Gavin,

Looks like SQL Server is thinking "CDR_DATA_2007-09-12" is a numeric operation of "CDR_DATA_2007" - 09 - 12.

Try escaping the table name with '[]'. So the SELECT statement will look like:

"select * from [CDR_DATA_2007-09-12]"

HTH,

Jimmy

|||Hi Jimmy,

Thanks it worked.

Cheers,
Gavin

JDBC Driver issues on Windows 2003

I'm running an application the is running on Weblogic 8.1.4 and uses MS SQL
Server 2000 as the db. I used the MS SQL JDBC driver SP3 and everything works
fine on a Windows 2000 box.
Now when I installed the same app on a Windows 2003 Enterprise Server, I get
the following errors,
<Jun 22, 2005 5:20:21 PM MDT> <Warning> <JDBC> <BEA-001129> <Received
exception
while creating connection for pool "BizLogicDBPool": [Microsoft][SQLServer
2000
Driver for JDBC][SQLServer]Cannot load the DLL sqljdbc.dll, or one of the
DLLs i
t references. Reason: 126(The specified module could not be found.).>
I tried adding sqljdbc.dll in the classpath, in the app startup script and
no use. Any ideas, pointers, help ? Thanks in advance for all your help.
Dallas Cowboy wrote:

> I'm running an application the is running on Weblogic 8.1.4 and uses MS SQL
> Server 2000 as the db. I used the MS SQL JDBC driver SP3 and everything works
> fine on a Windows 2000 box.
> Now when I installed the same app on a Windows 2003 Enterprise Server, I get
> the following errors,
> <Jun 22, 2005 5:20:21 PM MDT> <Warning> <JDBC> <BEA-001129> <Received
> exception
> while creating connection for pool "BizLogicDBPool": [Microsoft][SQLServer
> 2000
> Driver for JDBC][SQLServer]Cannot load the DLL sqljdbc.dll, or one of the
> DLLs i
> t references. Reason: 126(The specified module could not be found.).>
> I tried adding sqljdbc.dll in the classpath, in the app startup script and
> no use. Any ideas, pointers, help ? Thanks in advance for all your help.
Hi. In order to do XA calls, the driver requires that the sqljdbc.dll
be installed *at the DBMS*. It is accessed directly by the DBMS via
extended stored procedures which should be also installed in the DBMS.
Please see our documents on the MS SQLserver driver.
I hope this helps,
Joe Weinstein at BEA

JDBC Driver for SQLServer 7

Is there a JDBC Driver for SQLServer 7 available?
| Content-Class: urn:content-classes:message
| From: "Paul" <anonymous@.discussions.microsoft.com>
| Sender: "Paul" <anonymous@.discussions.microsoft.com>
| Subject: JDBC Driver for SQLServer 7
| Date: Thu, 7 Oct 2004 05:01:11 -0700
| Lines: 1
| Message-ID: <1eb601c4ac65$563a6b40$a601280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| thread-index: AcSsZVY66ayn1QWKRXuMw7WcnVfXAQ==
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:6327
| NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Is there a JDBC Driver for SQLServer 7 available?
|
Hi Paul,
Microsoft only ships a SQL Server 2000 compatible JDBC driver. For
connectivity to SQL Server 7, you will have to look into a third-party JDBC
driver for this functionality.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

JDBC driver for SQLServer 2000 SP4

Can anyone tell me whether it is better to use the JDBC driver 2000 for SQL Server 2000 + SP4 or is it better to use the newest JDBC driver 2005 ? What would be the advantage or disadvantage.

Thanks, Marcel

Marcel:

The main benefit of the new driver is that it's in active development with a full team working on new driver updates and features for the latest Java vms and SQL Server database versions. More importantly, the older SQL Server 2000 driver is in Extended Support mode and is not actively serviced, while SQL Server 2005 driver is actively supported and the development team can be much more responsive on customer issues.

Other key benefits of the SQL Server 2005 driver are that it is

-faster

-more secure (i.e. integrated authentication feature)

-supported against SQL Server 2005 with the new database features (i.e. db mirroring support)

-freely redistributable

Unless you have existing applications that depend on a feature tic from the older driver and aren't worth upgrading to use the new driver, we strongly recommend that you use the newer driver.

-shelby

Microsoft SQL Server Data Programmability

JDBC driver for SQLServer 2000 SP4

Can anyone tell me whether it is better to use the JDBC driver 2000 for SQL Server 2000 + SP4 or is it better to use the newest JDBC driver 2005 ? What would be the advantage or disadvantage.

Thanks, Marcel

Marcel:

The main benefit of the new driver is that it's in active development with a full team working on new driver updates and features for the latest Java vms and SQL Server database versions. More importantly, the older SQL Server 2000 driver is in Extended Support mode and is not actively serviced, while SQL Server 2005 driver is actively supported and the development team can be much more responsive on customer issues.

Other key benefits of the SQL Server 2005 driver are that it is

-faster

-more secure (i.e. integrated authentication feature)

-supported against SQL Server 2005 with the new database features (i.e. db mirroring support)

-freely redistributable

Unless you have existing applications that depend on a feature tic from the older driver and aren't worth upgrading to use the new driver, we strongly recommend that you use the newer driver.

-shelby

Microsoft SQL Server Data Programmability