execure store procedure from Query Analizer

Hi,
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
--
Message posted via http://www.sqlmonster.comHi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.developersdex.com/gurus/default.asp?p=4223
---
"Isambella via SQLMonster.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.sqlmonster.com
>|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrés Taylor|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
--
Message posted via http://www.sqlmonster.com|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example below.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via SQLMonster.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.sqlmonster.com
>sql

EXEC Strangeness

Hi all,

I would be very grateful if anyone can detect the problem with the following code. I have indicated the offending line. Basically if I hardcode in @.BelegId, it runs with no problems.

Declare @.SQL VarChar(1000)
Declare @.TableName VarChar(1000)
Declare @.Result Varchar(50)
Declare @.BelegId int
Set @.BelegId=447
Set @.ObjName='s_jps.ap_gr'

if (object_id (N'tempdb..#tempt') is not null) drop table #tempt;
create table #TempT (result varchar(1000))
--SELECT @.SQL='DECLARE @.RESULT VARCHAR(1000) '

SELECT @.SQL = 'SELECT CONVERT(VARCHAR(1000),' + @.ObjName + ') FROM '
SELECT @.SQL = @.SQL + 's_jps '
***PROBLEMATIC LINE FOLLOWS: IF I HARDCODE BELEGID, NO PROBLEMS***
SELECT @.Sql= @.Sql + 'WHERE de_beleg_id=' + @.BelegId
SELECT @.sql = @.sql + ' and s_se_art_id=5'
INSERT #TempT Exec ( @.SQL )
SELECT * FROM #TempT
GO

He's a quick fix without knowing anything else about your environment...

Change Declare @.BelegId int to Declare @.BelegId varchar(5)

SQLDiva

|||SELECT @.Sql= @.Sql + 'WHERE de_beleg_id=' + convert(varchar(10), @.BelegId)|||Please take a look at sp_executesql. It is safer to use sp_executesql than concatenating strings to execute in dynamic SQL. You need to protect the code against SQL injection attacks. And you should avoid dynamic SQL in this case if it is easier to just use a static SELECT statement specifying the table name. You probably don't need a temporary table also unless you are doing some complex processing.|||Thanks for the advice. I already looked into sp_executesql but had all sorts of problems with output parameters.

And I'm afraid that the unfortunate truth is that I do have to do some complex processing.

EXEC stored procedure

I have a stored procedure that creates temp tables. It has two parameters,
@.tablename and @.type. I execute it with the following command.
EXEC _sp_CreateTempTable 'TableA', 'T'
With these parameters, this will create a temp table named ##TMP_TableA
based on the original table TableA.
The problem is when I run this on one of our servers it works and when I run
it on another I get the following error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Help!
Dave
Can you post the proc?
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:5Ingh.8974$495.126@.trnddc06...
>I have a stored procedure that creates temp tables. It has two parameters,
>@.tablename and @.type. I execute it with the following command.
> EXEC _sp_CreateTempTable 'TableA', 'T'
> With these parameters, this will create a temp table named ##TMP_TableA
> based on the original table TableA.
> The problem is when I run this on one of our servers it works and when I
> run it on another I get the following error.
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
> Help!
> Dave
>
|||Unfortunately not. It is proprietary information.
Do you think the error is coming from within the executed proc?
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
> Can you post the proc?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>
|||> Do you think the error is coming from within the executed proc?
Yes. I suspect the proc is executing a dynamic SQL statement (since you are
passing the table name parameter) and that's where the error is.
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:6Ungh.9006$495.7049@.trnddc06...
> Unfortunately not. It is proprietary information.
> Do you think the error is coming from within the executed proc?
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
>
|||I have a solution for you but unfortunately can't post it because it's
proprietary.
Just kidding. Try this and see if it works any better:
EXEC _sp_CreateTempTable @.tablename = 'TableA', @.type = 'T'
Make sure the SP is the same version (accepts the same # of parameters) on
both servers.
"DaveF" <dave@.aol.com> wrote in message news:5Ingh.8974$495.126@.trnddc06...
>I have a stored procedure that creates temp tables. It has two parameters,
>@.tablename and @.type. I execute it with the following command.
> EXEC _sp_CreateTempTable 'TableA', 'T'
> With these parameters, this will create a temp table named ##TMP_TableA
> based on the original table TableA.
> The problem is when I run this on one of our servers it works and when I
> run it on another I get the following error.
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
> Help!
> Dave
>
|||Thanks for the idea but that didn't work.
I decided that since it worked on one machine and not the other that I would
compare Management Studio options and sure enough I found a difference. I
made the change and the error went away. Don't ask me why this particular
change fixed it because I don't know why.
Options-->Query Execution-->SQL Server-->Advanced
check SET CONCAT_NULL_YIELDS_NULL
Dave
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23loaRX$HHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I have a solution for you but unfortunately can't post it because it's
>proprietary.
> Just kidding. Try this and see if it works any better:
> EXEC _sp_CreateTempTable @.tablename = 'TableA', @.type = 'T'
> Make sure the SP is the same version (accepts the same # of parameters) on
> both servers.
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>
|||"DaveF" <dave@.aol.com> wrote in message news:i2Hgh.51$hy6.13@.trnddc05...
> Thanks for the idea but that didn't work.
> I decided that since it worked on one machine and not the other that I
> would compare Management Studio options and sure enough I found a
> difference. I made the change and the error went away. Don't ask me why
> this particular change fixed it because I don't know why.
> Options-->Query Execution-->SQL Server-->Advanced
> check SET CONCAT_NULL_YIELDS_NULL
Sounds like there's a NULL value being concatenated into a string somewhere
in your SP, most likely in some dynamic SQL you're trying to execute. That
setting controls null concatenation in strings. When it's set to ON,
concatenating NULL to a string returns NULL. When it's set to OFF, when a
NULL is concatenated to a string it's treated like '' - an empty string.
Here's an example that shows the difference:
DECLARE @.table VARCHAR(255)
SELECT @.table = 'syscomments'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: syscomments', @.sql
SET CONCAT_NULL_YIELDS_NULL ON
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
GO
SET CONCAT_NULL_YIELDS_NULL ON
When CONCAT_NULL_YIELDS_NULL is ON, appending NULL to a string returns NULL.
SQL Server will EXEC a NULL dynamic SQL string without complaint. When
CONCAT_NULL_YIELDS_NULL is OFF, the table name in this SELECT query is
appended as an empty string which will cause an error when SQL Server tries
to execute it.
Based on what you said, it sounds like one of the dynamic SQL statements
you're trying to execute has a NULL value appended to it somewhere, and
that's causing your error when that setting is OFF. It also means that that
particular dynamic SQL statement you think is being executed is not being
executed. Might want to check all your dynamic SQL statements that should
be executing (SELECT or PRINT them out right before the EXEC statement).
sql

EXEC stored procedure

I have a stored procedure that creates temp tables. It has two parameters,
@.tablename and @.type. I execute it with the following command.
EXEC _sp_CreateTempTable 'TableA', 'T'
With these parameters, this will create a temp table named ##TMP_TableA
based on the original table TableA.
The problem is when I run this on one of our servers it works and when I run
it on another I get the following error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Help!
DaveCan you post the proc?
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:5Ingh.8974$495.126@.trnddc06...
>I have a stored procedure that creates temp tables. It has two parameters,
>@.tablename and @.type. I execute it with the following command.
> EXEC _sp_CreateTempTable 'TableA', 'T'
> With these parameters, this will create a temp table named ##TMP_TableA
> based on the original table TableA.
> The problem is when I run this on one of our servers it works and when I
> run it on another I get the following error.
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
> Help!
> Dave
>|||Unfortunately not. It is proprietary information.
Do you think the error is coming from within the executed proc?
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
> Can you post the proc?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>|||> Do you think the error is coming from within the executed proc?
Yes. I suspect the proc is executing a dynamic SQL statement (since you are
passing the table name parameter) and that's where the error is.
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:6Ungh.9006$495.7049@.trnddc06...
> Unfortunately not. It is proprietary information.
> Do you think the error is coming from within the executed proc?
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
>|||Thanks for the idea but that didn't work.
I decided that since it worked on one machine and not the other that I would
compare Management Studio options and sure enough I found a difference. I
made the change and the error went away. Don't ask me why this particular
change fixed it because I don't know why.
Options-->Query Execution-->SQL Server-->Advanced
check SET CONCAT_NULL_YIELDS_NULL
Dave
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23loaRX$HHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I have a solution for you but unfortunately can't post it because it's
>proprietary.
> Just kidding. Try this and see if it works any better:
> EXEC _sp_CreateTempTable @.tablename = 'TableA', @.type = 'T'
> Make sure the SP is the same version (accepts the same # of parameters) on
> both servers.
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>|||"DaveF" <dave@.aol.com> wrote in message news:i2Hgh.51$hy6.13@.trnddc05...
> Thanks for the idea but that didn't work.
> I decided that since it worked on one machine and not the other that I
> would compare Management Studio options and sure enough I found a
> difference. I made the change and the error went away. Don't ask me why
> this particular change fixed it because I don't know why.
> Options-->Query Execution-->SQL Server-->Advanced
> check SET CONCAT_NULL_YIELDS_NULL
Sounds like there's a NULL value being concatenated into a string somewhere
in your SP, most likely in some dynamic SQL you're trying to execute. That
setting controls null concatenation in strings. When it's set to ON,
concatenating NULL to a string returns NULL. When it's set to OFF, when a
NULL is concatenated to a string it's treated like '' - an empty string.
Here's an example that shows the difference:
DECLARE @.table VARCHAR(255)
SELECT @.table = 'syscomments'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: syscomments', @.sql
SET CONCAT_NULL_YIELDS_NULL ON
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
GO
SET CONCAT_NULL_YIELDS_NULL ON
When CONCAT_NULL_YIELDS_NULL is ON, appending NULL to a string returns NULL.
SQL Server will EXEC a NULL dynamic SQL string without complaint. When
CONCAT_NULL_YIELDS_NULL is OFF, the table name in this SELECT query is
appended as an empty string which will cause an error when SQL Server tries
to execute it.
Based on what you said, it sounds like one of the dynamic SQL statements
you're trying to execute has a NULL value appended to it somewhere, and
that's causing your error when that setting is OFF. It also means that that
particular dynamic SQL statement you think is being executed is not being
executed. Might want to check all your dynamic SQL statements that should
be executing (SELECT or PRINT them out right before the EXEC statement).

EXEC stored procedure

I have a stored procedure that creates temp tables. It has two parameters,
@.tablename and @.type. I execute it with the following command.
EXEC _sp_CreateTempTable 'TableA', 'T'
With these parameters, this will create a temp table named ##TMP_TableA
based on the original table TableA.
The problem is when I run this on one of our servers it works and when I run
it on another I get the following error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Help!
DaveCan you post the proc?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:5Ingh.8974$495.126@.trnddc06...
>I have a stored procedure that creates temp tables. It has two parameters,
>@.tablename and @.type. I execute it with the following command.
> EXEC _sp_CreateTempTable 'TableA', 'T'
> With these parameters, this will create a temp table named ##TMP_TableA
> based on the original table TableA.
> The problem is when I run this on one of our servers it works and when I
> run it on another I get the following error.
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
> Help!
> Dave
>|||Unfortunately not. It is proprietary information.
Do you think the error is coming from within the executed proc?
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
> Can you post the proc?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>>I have a stored procedure that creates temp tables. It has two
>>parameters, @.tablename and @.type. I execute it with the following command.
>> EXEC _sp_CreateTempTable 'TableA', 'T'
>> With these parameters, this will create a temp table named ##TMP_TableA
>> based on the original table TableA.
>> The problem is when I run this on one of our servers it works and when I
>> run it on another I get the following error.
>> Msg 102, Level 15, State 1, Line 1
>> Incorrect syntax near ','.
>> Help!
>> Dave
>|||> Do you think the error is coming from within the executed proc?
Yes. I suspect the proc is executing a dynamic SQL statement (since you are
passing the table name parameter) and that's where the error is.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:6Ungh.9006$495.7049@.trnddc06...
> Unfortunately not. It is proprietary information.
> Do you think the error is coming from within the executed proc?
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
>> Can you post the proc?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "DaveF" <dave@.aol.com> wrote in message
>> news:5Ingh.8974$495.126@.trnddc06...
>>I have a stored procedure that creates temp tables. It has two
>>parameters, @.tablename and @.type. I execute it with the following
>>command.
>> EXEC _sp_CreateTempTable 'TableA', 'T'
>> With these parameters, this will create a temp table named ##TMP_TableA
>> based on the original table TableA.
>> The problem is when I run this on one of our servers it works and when I
>> run it on another I get the following error.
>> Msg 102, Level 15, State 1, Line 1
>> Incorrect syntax near ','.
>> Help!
>> Dave
>>
>|||I have a solution for you but unfortunately can't post it because it's
proprietary.
Just kidding. Try this and see if it works any better:
EXEC _sp_CreateTempTable @.tablename = 'TableA', @.type = 'T'
Make sure the SP is the same version (accepts the same # of parameters) on
both servers.
"DaveF" <dave@.aol.com> wrote in message news:5Ingh.8974$495.126@.trnddc06...
>I have a stored procedure that creates temp tables. It has two parameters,
>@.tablename and @.type. I execute it with the following command.
> EXEC _sp_CreateTempTable 'TableA', 'T'
> With these parameters, this will create a temp table named ##TMP_TableA
> based on the original table TableA.
> The problem is when I run this on one of our servers it works and when I
> run it on another I get the following error.
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
> Help!
> Dave
>|||Thanks for the idea but that didn't work.
I decided that since it worked on one machine and not the other that I would
compare Management Studio options and sure enough I found a difference. I
made the change and the error went away. Don't ask me why this particular
change fixed it because I don't know why.
Options-->Query Execution-->SQL Server-->Advanced
check SET CONCAT_NULL_YIELDS_NULL
Dave
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23loaRX$HHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I have a solution for you but unfortunately can't post it because it's
>proprietary.
> Just kidding. Try this and see if it works any better:
> EXEC _sp_CreateTempTable @.tablename = 'TableA', @.type = 'T'
> Make sure the SP is the same version (accepts the same # of parameters) on
> both servers.
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>>I have a stored procedure that creates temp tables. It has two
>>parameters, @.tablename and @.type. I execute it with the following command.
>> EXEC _sp_CreateTempTable 'TableA', 'T'
>> With these parameters, this will create a temp table named ##TMP_TableA
>> based on the original table TableA.
>> The problem is when I run this on one of our servers it works and when I
>> run it on another I get the following error.
>> Msg 102, Level 15, State 1, Line 1
>> Incorrect syntax near ','.
>> Help!
>> Dave
>|||"DaveF" <dave@.aol.com> wrote in message news:i2Hgh.51$hy6.13@.trnddc05...
> Thanks for the idea but that didn't work.
> I decided that since it worked on one machine and not the other that I
> would compare Management Studio options and sure enough I found a
> difference. I made the change and the error went away. Don't ask me why
> this particular change fixed it because I don't know why.
> Options-->Query Execution-->SQL Server-->Advanced
> check SET CONCAT_NULL_YIELDS_NULL
Sounds like there's a NULL value being concatenated into a string somewhere
in your SP, most likely in some dynamic SQL you're trying to execute. That
setting controls null concatenation in strings. When it's set to ON,
concatenating NULL to a string returns NULL. When it's set to OFF, when a
NULL is concatenated to a string it's treated like '' - an empty string.
Here's an example that shows the difference:
DECLARE @.table VARCHAR(255)
SELECT @.table = 'syscomments'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: syscomments', @.sql
SET CONCAT_NULL_YIELDS_NULL ON
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
GO
SET CONCAT_NULL_YIELDS_NULL ON
When CONCAT_NULL_YIELDS_NULL is ON, appending NULL to a string returns NULL.
SQL Server will EXEC a NULL dynamic SQL string without complaint. When
CONCAT_NULL_YIELDS_NULL is OFF, the table name in this SELECT query is
appended as an empty string which will cause an error when SQL Server tries
to execute it.
Based on what you said, it sounds like one of the dynamic SQL statements
you're trying to execute has a NULL value appended to it somewhere, and
that's causing your error when that setting is OFF. It also means that that
particular dynamic SQL statement you think is being executed is not being
executed. Might want to check all your dynamic SQL statements that should
be executing (SELECT or PRINT them out right before the EXEC statement).

Exec Stored Proc

I need some help with the following store proc, something is wrong but I just dont see it.
Thanks
btw I am no expert at sp so something might be complety wrong.

ALTER PROCEDURE dbo.GetSearchByDateRange

(
@.strColumnNamenvarchar (50),
@.dtDate1 Date,
@.dtDate2 Date
)
as

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName + ' BETWEEN ' + ''' + @.dtDate1 + ''' + ' AND ' + ''' + @.dtDate2 + '''')i havent tried it but check if this works:

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName +
 ' BETWEEN ' + '''' + @.dtDate1 + '''' + ' AND ' + '''' + @.dtDate2 + ''''

hth|||Try this:

ALTER PROCEDURE dbo.GetSearchByDateRange

(
@.strColumnName nvarchar (50),
@.dtDate1 DateTime,
@.dtDate2 DateTime
)
as

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName + ' BETWEEN ''' + @.dtDate1 + ''' AND ''' + @.dtDate2 + '''')|||How many possible values for @.strColumnName could there be? For performance and code reliability reasons I would write it like below. That way the SQL is precompiled (allowing the db engine to skip a step during execution and also allowing syntax errors to be spotted at the time of sp creation instead of execution.

ALTER PROCEDURE dbo.GetSearchByDateRange
(
@.strColumnName nvarchar (50),
@.dtDate1 DateTime,
@.dtDate2 DateTime
)
as
 if (@.strColumnName = 'Birthdate')
 begin
 SELECT * FROM Customers
 WHERE Birthdate BETWEEN @.dtDate1 AND @.dtDate2
 return 0
 end
 if (@.strColumnName = 'LastPayment')
 begin
 SELECT * FROM Customers
 WHERE 'LastPayment'BETWEEN @.dtDate1 AND @.dtDate2
 return 0
 end
 ETC...

|||Thank you all for the infos.
Corbi I think you had a good point there and I will definatly take it in consideration.

Oh last thing how do I control the injection of a date from my code to the store proc if my db is using small dates? I see that it can cause problem if I don't send the right format to the sp.
Thanks again|||If possible, i would change the stored procedure parameters to "smalldatetime", process the different date formats in your application and convert your userinput to "smalldatetime" there.

Hth,

Moon|||the only things that need to be considered in the Execute(Whatever Valid SQL Query in string format) is to have double single qoutes for each date value in the query, and that those date values are converted to a valid string (this is because you need to concatenate a string, so try this

Declare @.SQLString As VarChar(8000)

Set @.SQLString = 'SELECT * FROM Employees WHERE ' + @.strColumnName + ' BETWEEN ''' + Convert(Char(8), @.dtDate1, 112) + ''' AND ''' + Convert(Char(8), @.dtDate2, 112) + ''''

Exec (@.SQLString)

This will Work fine

Delfino III Salinas Sepúlveda
DSS Hi-Tech, México
diiisalinas@.prodigy.net.mx|||Also use DateTime or smalldatetime types.

EXEC sp_attach_db have a error

I entered the following in the Query Analyzer and got the result shown:

EXEC sp_attach_db @.dbname = N'DS2004',
@.filename1 = N'd:\ds2004_data.mdf',
@.filename2 = N'd:\ds2004_log.Ldf'

Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'd:\ds2004_data.MDF' is not a valid database file header. The PageAudit property is incorrect

I can find nothing about PageAudit property.

What is it and how do I need to modify the command?

Many thanksI believe the message is trying to tell you that the PageAudit property of the database file header property in ds2004_data.mdf is bad. I think your command is okay, but you have a corrupted file.|||Maybe you're trying to detach a SQL 2k db and restore to SQL 7.0? It doesn't work. Attach or restore a sql 2000 database to version 7.0 is not supported.

exec sp with output parameter

I have the following sp:
ALTER PROCEDURE myspPrepareTextForHTML
@.MessageContent nvarchar(1400),
@.returnval nvarchar(1400) output
AS
BEGIN
SET NOCOUNT ON;
SET @.returnval='something'
RETURN @.returnval
END

When I try this:

EXEC myspPrepareTextForHTML @.MessageContent='dfgsdfgdf', @.returnvalOUTPUT

print @.returnval

I get the error:
Must declare the scalar variable "@.returnval".

How can I get this to work?

You need to set the value of an output parameter. In this case, you jus want to pass in your current variable.

Just like other parameters, the left side of = means the name of the parameter, right side means the value, or return value (return variable).

EXEC myspPrepareTextForHTML @.MessageContent='dfgsdfgdf', @.returnval = @.returnvalOUTPUT

|||now I get the error:

Msg 245, Level 16, State 1, Procedure myspPrepareTextForHTML, Line 17

Conversion failed when converting the nvarchar value 'something' to data type int.

|||

Peter Smith:

now I get the error:

Msg 245, Level 16, State 1, Procedure myspPrepareTextForHTML, Line 17

Conversion failed when converting the nvarchar value 'something' to data type int.

Most likely, the calling proc or SQL statement declaredits@.returnval as an int, rather than an nvarchar of the same size as declared in the myspPrepareTextForHTML proc.

|||This is everything I have so far...I dont see why I still get that int error:

ALTER

PROCEDURE [dbo].[myspPrepareTextForHTML]

@.MessageContent

nvarchar(1400),

@.returnval

nvarchar(1400)output

AS

BEGIN

SETNOCOUNTON;SET @.returnval='something'RETURN @.returnval

END

declare

@.returnvalnvarchar(1400)

EXEC

myspPrepareTextForHTML @.MessageContent='dfgsdfgdf', @.returnval= @.returnvalOUTPUT

print

@.returnval|||Because anything you return with RETURN can only be of INT type. So SQL Server is trying to convert the 'something' to int. If you are returning the value through the OUTPUT parameter you dont need to use the RETURN statement. So remove theRETURN @.returnval statement in your proc and you should be fine.|||

Sorry, I missed the fact that you are trying to return an output value. Any value returned with the RETURN statement must be an int. This is why there are OUTPUT variables, which allow us to essentially 'return' values of any type (like using reference/ByRef variables in .NET).

Change your code to the following, and save the RETURN statement to return something similar to an error code:

ALTER PROCEDURE [dbo].[myspPrepareTextForHTML] @.MessageContentnvarchar(1400), @.returnvalnvarchar(1400)output ASBEGIN SET NOCOUNT ON;SET @.returnval='something'RETURN 0-- no errorEND

|||like a charm!

Exec SP many times, from select?

Hi All...

if i had the following sp...

*******************************************************
create procedure my_insert (param1 int, param2 int, paramx int)
as
...
complicated insert routine
...
return
*******************************************************

and then i wanted to exec this sp in another procedure i would have

exec my_insert( 1_value, 2_value, 3_value )

My question is how could i exec this will the result set of a select... something like this

exec my_insert (select 1_value, 2_value, 3_value from another_table).

I know i could have this in an insert result type statement ie...

insert into dest_table (select 1_value, 2_value, 3_value from another_table)

but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement

Many Thanks
Gary Tyou can get the values into variables and send the variables into the sp.

select @.var1=select1_Value,@.var2=select2_Value,@.var3=select3_Value from YourTable
exec my_insert(@.var1,@.var2,@.var2)

hth|||Thanks for your help....but

This statement would only call the stored procedure once with the params set to the last selected values.

I would like to call the sp as many times as there are records in the select statement, containing the values for each record.

Cheers
Gary T|||Hi All,

If it's any help to describe the problem, I current carry out this by using a cursor, but I thought there must be an easier (better) way of doing this.

*****************************************************
DECLARE my_cursor CURSOR FOR
SELECT 1_value, 2_value, 3_value from other_table where some_condition

OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO
@.var_1, @.var_2, @.var_3
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec my_insert @.var_1, @.var_2, @.var_3
FETCH NEXT FROM my_cursor
INTO
@.var_1, @.var_2, @.var_3
END
CLOSE my_cursor
DEALLOCATE my_cursor
*****************************************************

Cheers
Gary T|||cursors are usually a performance hit, but unless you are processing thousands of rows, it shud be ok. make sure you declare your cursor as :

DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT
OPEN rs
fetch next from rs into
WHILE ( @.@.FETCH_STATUS = 0 )
begin
FETCH NEXT FROM rs INTO
END
close rs
deallocate rs

the local,forward_only etc are optional parameters but will make it run faster. check out BOL for more info abt cursors and other parameters.

hthsql

EXEC date problem

Hello all,
I have a standard stored procedure that contains the following code:

SET @.SQL = 'SELECT id, building, firstname, lastname, closed, assignto, vandalism, entered_by, phone, roomno, status, dateentered, date_closed AS DATECLOSED FROM requests '
SET @.SQL = @.SQL + 'WHERE building_no IN (' + @.BUILDINGLIST + ') AND STATUS_NO = ' + @.STATUS + ' '

Works fine, but my dates come back unformated i want to replace the above:

dateentered and date_closed

with

CONVERT(varchar(30), dateentered, 101) AS DATEENTERED
CONVERT(varchar(30), date_closed, 101) AS DATECLOSED

but, when i do, i receive the following error:

ERROR CLOSE TO AS

Does anyone have any ideas? Thanks in advance.

Richard M.what's it look like when you've already replaced them?

CONVERT(varchar(30), dateentered, 101) AS DATEENTERED

also, dateentered appears to be a field that doesn't exist.

exclusive set of data query

hi all, given that i have the following tables and data:
mst_locs.locid ctl_loctypes.loctypeid [description]
1 1 [plant]
2 2 [hub]
3 3 [warehouse]
intersect_loc_loctype
locid loctypeid
1 1
1 2
1 3
2 1
2 2
3 3
i want to query out exclusive loctypes data for loctypeid = 3. using the sql
:
select * from intersect_loc_loctype where loctypeid = 3
will return:
locid loctypeid
1 3
3 3
i need a way to loc that are exclusively loctype = 3 [warehouse] such that
the returned data will be:
locid loctypeid
3 3
this is a bit long but i hope my problem was stated clearly.Here's one way:
SELECT *
FROM intersect_loc_loctype ill
WHERE loctypeid = 3
AND NOT EXISTS
(
SELECT *
FROM intresect_loc_loctype ill2
WHERE ill2.locid = ill.locid
AND ill2.loctype <> 3
)
To solve problems like this going forward, I recommend you read the
following article:
http://www.dbazine.com/ofinterest/oi-articles/celko1
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Amil" <Amil@.discussions.microsoft.com> wrote in message
news:4E1F7C1E-8C48-4EEE-BD4B-1477A2F4ADC0@.microsoft.com...
> hi all, given that i have the following tables and data:
> mst_locs.locid ctl_loctypes.loctypeid [description]
> 1 1 [plant]
> 2 2 [hub]
> 3 3 [warehouse]
> intersect_loc_loctype
> locid loctypeid
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 3
> i want to query out exclusive loctypes data for loctypeid = 3. using the
sql:
> select * from intersect_loc_loctype where loctypeid = 3
> will return:
> locid loctypeid
> 1 3
> 3 3
> i need a way to loc that are exclusively loctype = 3 [warehouse] such that
> the returned data will be:
> locid loctypeid
> 3 3
> this is a bit long but i hope my problem was stated clearly.|||Adam,
Thank you for the solution, and thank you for providing me the link. You
helped me a great deal.
"Adam Machanic" wrote:

> Here's one way:
>
> SELECT *
> FROM intersect_loc_loctype ill
> WHERE loctypeid = 3
> AND NOT EXISTS
> (
> SELECT *
> FROM intresect_loc_loctype ill2
> WHERE ill2.locid = ill.locid
> AND ill2.loctype <> 3
> )
>
> To solve problems like this going forward, I recommend you read the
> following article:
> http://www.dbazine.com/ofinterest/oi-articles/celko1
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Amil" <Amil@.discussions.microsoft.com> wrote in message
> news:4E1F7C1E-8C48-4EEE-BD4B-1477A2F4ADC0@.microsoft.com...
> sql:
>
>sql

Exclusive OR condition needed for Check Constraint

I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksLaurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
--
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:
> >
> > I want to put a check constraint on a table to enforce the following
> > condition:
> >
> > Of four columns A, B, C, D one must have a value and the other three must be
> > null.
> >
> > A is a varchar column, the others are integer.
> >
> > It seemed like I needed to create a bitwise Exclusive OR expression, with
> > some function to return true/false for each column having a value/Null.
> >
> > The best I could do was this:
> >
> > ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> >
> > This stops inserts when 0, 2 or 4 of the columns have values, and allows
> > inserts when 1 column has a value (which is correct). However it also allows
> > inserts when 3 of the columns have values. I have no idea why. Can anyone
> > fix the expression, or give me an alternative expression that fits the
> > requirements?
> >
> > Thanks|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>

Exclusive Join

I have a link table between a user table and a lookup that contains the following data

ID Role
-- --
92166 1
92166 11
92167 7
92167 11
92210 3
92210 7
92210 11
92211 7
92211 11
92212 7
92212 11
92213 7
92213 11

92213 3

The Link table stores whether a user has a combination of rolea. I need to be able to ask for any given user IE 92210 has the roles 3 and 7 any additional roles are ok but they must have all of the combinations I am asking for. The role combinations can be anywhere from 1 to 40. So in one case i have to ask for roles (3,7) and in another (1,7,11) The only way I can figure out how to do this is a dynamic self join or by using a cursor (yuck).

This is a legacy application and the requirement changed from roles being inclusive to exclusive and a table structure change is out of the question.

There must be a better way. Any help would be appreciated.

Thanks in advance

You could do something like joining to the passed array with using a split function which is wrote sometime ago:

CREATE FUNCTION dbo.Split
(
@.String VARCHAR(200),
@.Delimiter VARCHAR(5)
)
RETURNS @.SplittedValues TABLE
(
OccurenceId SMALLINT IDENTITY(1,1),
SplitValue VARCHAR(200)
)
AS
BEGIN
DECLARE @.SplitLength INT

WHILE LEN(@.String) > 0
BEGIN
SELECT @.SplitLength = (CASE CHARINDEX(@.Delimiter,@.String) WHEN 0 THEN
LEN(@.String) ELSE CHARINDEX(@.Delimiter,@.String) -1 END)

INSERT INTO @.SplittedValues
SELECT SUBSTRING(@.String,1,@.SplitLength)

SELECT @.String = (CASE (LEN(@.String) - @.SplitLength) WHEN 0 THEN ''
ELSE RIGHT(@.String, LEN(@.String) - @.SplitLength - 1) END)
END
RETURN
END

This evaluates to:

DELCARE @.SomeValues VARCHAR(100)
SET @.SomeValues = '1,7,11'

SELECT 'Access Granted'
FROM SomeTable ST
INNER JOIN dbo.Split(@.SomeValues,',') S ON
ON S.SplitValue = ST.Role
WHERE ID = 95321
HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.Split(SomeValues,','))

This is untested and maybe need to be modified, try to use that in the case you don′t wanna use dynamic sql.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks, that put me on the right track and now have a udf that does the job

sql

Excessive Free Space in Large Table

Hello all,
I'm having the following space allocation issue with a large table in a SQL
Server 2000 DB.
I have a table which stores visits to a site, currently with 95 million
rows. This table currently has a total of 60GB space: 24GB used space, 4GB
index space, and 32 GB unused space. The server has been running out of
space recently, and I would like to reclaim alot of the unused space from
this table. The table definition is as follows:
visitor_id int 4
visitor_settings varchar 7000
short_settings tinyint 1
visit_count int 4
created_reason tinyint 1
modified_by int 4
modified_date datetime 8
created_by int 4
created_date datetime 8
GUID_for_clustering uniqueidentifier 16
There are 2 indexes, a PK index on visitor_id, and a clustered index on
GUID_for_clustering, both with a 50% fill factor. Shrinking the table helps
temporarily, but the unused space climbs back up to 30GB after reindexing
the table. Any thoughts on what is causing the allocation of all the extra
free space, and how I can reduce it? My initial thoughts relate to
adjusting the fill factor, or possibly modifying the visitor_settings
varchar 7000 field (perhaps 'text' would actually reduce the space
allocation?). Any thoughts would be much appreciated.
Thanks!
-MarkMark,
What exactly do you mean when you say "Guid for clustering"? Are you
talking about a Clustered Index? If so then why would you want to do that?
A Guid is about the worst choice for a clustered index that there is.
Here's what I think your issues are other than the clustered index on the
guid. You need plenty of free space in the database for normal activities
such as Reindexing. As a matter of fact BOL recommends 1.2 times the size
of the table in free space before you reindex. That is mainly because sqls
erver will rebuild the table (if it has a clustered index on it) elsewhere
in the db and then drop the original table when done. So you need lots of
free and preferably contiguous space to do this. If you reindex a table and
then shrink the database you will most likely undo all that you accomplished
when you reindexed the table. To shrink the files sql server must clear
space at the end of the file inwards to shrink it. This means it will move
the data to unused space near the beginning. This usually results in
getting heavy fragmentation of the tables again. So if you need the space
you can't keep reindexing the tables otherwise it will just keep growing
again. The real solution is to get more disk space and always keep plenty
of free space available. If your really tight you can bcp out all the data,
truncate all the tables,Shrink the db and bcp it back in again.
Andrew J. Kelly
SQL Server MVP
"Mark Weidner" <mjw22@.drexel.edu> wrote in message
news:e9SLoI55DHA.2764@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hello all,
> I'm having the following space allocation issue with a large table in a

---

SQL

quote:

---

> Server 2000 DB.
> I have a table which stores visits to a site, currently with 95 million
> rows. This table currently has a total of 60GB space: 24GB used space,

---

4GB

quote:

---

> index space, and 32 GB unused space. The server has been running out of
> space recently, and I would like to reclaim alot of the unused space from
> this table. The table definition is as follows:
> visitor_id int 4
> visitor_settings varchar 7000
> short_settings tinyint 1
> visit_count int 4
> created_reason tinyint 1
> modified_by int 4
> modified_date datetime 8
> created_by int 4
> created_date datetime 8
> GUID_for_clustering uniqueidentifier 16
> There are 2 indexes, a PK index on visitor_id, and a clustered index on
> GUID_for_clustering, both with a 50% fill factor. Shrinking the table

---

helps

quote:

---

> temporarily, but the unused space climbs back up to 30GB after reindexing
> the table. Any thoughts on what is causing the allocation of all the

---

extra

quote:

---

> free space, and how I can reduce it? My initial thoughts relate to
> adjusting the fill factor, or possibly modifying the visitor_settings
> varchar 7000 field (perhaps 'text' would actually reduce the space
> allocation?). Any thoughts would be much appreciated.
> Thanks!
> -Mark
>

---

Excessive Free Space in Large Table

Hello all,
I'm having the following space allocation issue with a large table in a SQL
Server 2000 DB.
I have a table which stores visits to a site, currently with 95 million
rows. This table currently has a total of 60GB space: 24GB used space, 4GB
index space, and 32 GB unused space. The server has been running out of
space recently, and I would like to reclaim alot of the unused space from
this table. The table definition is as follows:
visitor_id int 4
visitor_settings varchar 7000
short_settings tinyint 1
visit_count int 4
created_reason tinyint 1
modified_by int 4
modified_date datetime 8
created_by int 4
created_date datetime 8
GUID_for_clustering uniqueidentifier 16
There are 2 indexes, a PK index on visitor_id, and a clustered index on
GUID_for_clustering, both with a 50% fill factor. Shrinking the table helps
temporarily, but the unused space climbs back up to 30GB after reindexing
the table. Any thoughts on what is causing the allocation of all the extra
free space, and how I can reduce it? My initial thoughts relate to
adjusting the fill factor, or possibly modifying the visitor_settings
varchar 7000 field (perhaps 'text' would actually reduce the space
allocation?). Any thoughts would be much appreciated.
Thanks!
-MarkIf all that space gets consume after reindexing then its
the transaction log. Read up on truncate/ shrink TLog.
>--Original Message--
>Hello all,
>I'm having the following space allocation issue with a
large table in a SQL
>Server 2000 DB.
>I have a table which stores visits to a site, currently
with 95 million
>rows. This table currently has a total of 60GB space:
24GB used space, 4GB
>index space, and 32 GB unused space. The server has been
running out of
>space recently, and I would like to reclaim alot of the
unused space from
>this table. The table definition is as follows:
>visitor_id int 4
>visitor_settings varchar 7000
>short_settings tinyint 1
>visit_count int 4
>created_reason tinyint 1
>modified_by int 4
>modified_date datetime 8
>created_by int 4
>created_date datetime 8
>GUID_for_clustering uniqueidentifier 16
>There are 2 indexes, a PK index on visitor_id, and a
clustered index on
>GUID_for_clustering, both with a 50% fill factor.
Shrinking the table helps
>temporarily, but the unused space climbs back up to 30GB
after reindexing
>the table. Any thoughts on what is causing the
allocation of all the extra
>free space, and how I can reduce it? My initial thoughts
relate to
>adjusting the fill factor, or possibly modifying the
visitor_settings
>varchar 7000 field (perhaps 'text' would actually reduce
the space
>allocation?). Any thoughts would be much appreciated.
>Thanks!
>-Mark
>
>.
>|||Because of space concerns, this database has no transaction log (it is
non-critical data, and is backed-up daily).
I can only think the extra space is being allocated for the visitor_settings
varchar 7000 on each row after reindexing, even though the average length is
probably only around 3000. Because it's a varchar type, the space would not
actually be used, but perhaps is still allocated and thus, unavailable?
-Mark
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:725101c3e793$8abb8620$a101280a@.phx.gbl...
> If all that space gets consume after reindexing then its
> the transaction log. Read up on truncate/ shrink TLog.
> >--Original Message--
> >Hello all,
> >
> >I'm having the following space allocation issue with a
> large table in a SQL
> >Server 2000 DB.
> >
> >I have a table which stores visits to a site, currently
> with 95 million
> >rows. This table currently has a total of 60GB space:
> 24GB used space, 4GB
> >index space, and 32 GB unused space. The server has been
> running out of
> >space recently, and I would like to reclaim alot of the
> unused space from
> >this table. The table definition is as follows:
> >
> >visitor_id int 4
> >visitor_settings varchar 7000
> >short_settings tinyint 1
> >visit_count int 4
> >created_reason tinyint 1
> >modified_by int 4
> >modified_date datetime 8
> >created_by int 4
> >created_date datetime 8
> >GUID_for_clustering uniqueidentifier 16
> >
> >There are 2 indexes, a PK index on visitor_id, and a
> clustered index on
> >GUID_for_clustering, both with a 50% fill factor.
> Shrinking the table helps
> >temporarily, but the unused space climbs back up to 30GB
> after reindexing
> >the table. Any thoughts on what is causing the
> allocation of all the extra
> >free space, and how I can reduce it? My initial thoughts
> relate to
> >adjusting the fill factor, or possibly modifying the
> visitor_settings
> >varchar 7000 field (perhaps 'text' would actually reduce
> the space
> >allocation?). Any thoughts would be much appreciated.
> >
> >Thanks!
> >
> >-Mark
> >
> >
> >.
> >|||Mark,
What exactly do you mean when you say "Guid for clustering"? Are you
talking about a Clustered Index? If so then why would you want to do that?
A Guid is about the worst choice for a clustered index that there is.
Here's what I think your issues are other than the clustered index on the
guid. You need plenty of free space in the database for normal activities
such as Reindexing. As a matter of fact BOL recommends 1.2 times the size
of the table in free space before you reindex. That is mainly because sqls
erver will rebuild the table (if it has a clustered index on it) elsewhere
in the db and then drop the original table when done. So you need lots of
free and preferably contiguous space to do this. If you reindex a table and
then shrink the database you will most likely undo all that you accomplished
when you reindexed the table. To shrink the files sql server must clear
space at the end of the file inwards to shrink it. This means it will move
the data to unused space near the beginning. This usually results in
getting heavy fragmentation of the tables again. So if you need the space
you can't keep reindexing the tables otherwise it will just keep growing
again. The real solution is to get more disk space and always keep plenty
of free space available. If your really tight you can bcp out all the data,
truncate all the tables,Shrink the db and bcp it back in again.
--
Andrew J. Kelly
SQL Server MVP
"Mark Weidner" <mjw22@.drexel.edu> wrote in message
news:e9SLoI55DHA.2764@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I'm having the following space allocation issue with a large table in a
SQL
> Server 2000 DB.
> I have a table which stores visits to a site, currently with 95 million
> rows. This table currently has a total of 60GB space: 24GB used space,
4GB
> index space, and 32 GB unused space. The server has been running out of
> space recently, and I would like to reclaim alot of the unused space from
> this table. The table definition is as follows:
> visitor_id int 4
> visitor_settings varchar 7000
> short_settings tinyint 1
> visit_count int 4
> created_reason tinyint 1
> modified_by int 4
> modified_date datetime 8
> created_by int 4
> created_date datetime 8
> GUID_for_clustering uniqueidentifier 16
> There are 2 indexes, a PK index on visitor_id, and a clustered index on
> GUID_for_clustering, both with a 50% fill factor. Shrinking the table
helps
> temporarily, but the unused space climbs back up to 30GB after reindexing
> the table. Any thoughts on what is causing the allocation of all the
extra
> free space, and how I can reduce it? My initial thoughts relate to
> adjusting the fill factor, or possibly modifying the visitor_settings
> varchar 7000 field (perhaps 'text' would actually reduce the space
> allocation?). Any thoughts would be much appreciated.
> Thanks!
> -Mark
>

Excessive blocking - runnable processes

Hi there,

Currently using SQL Server 2000 (SP4). The following condition started occurring last week:

- Server has excessive blocking
- Majority of the processes are in runnable state
- Excessive blocking happens for a few mins. and repeats again during the day. Does not happen at night.
- Nothing on the server errorlog, profiler
- CPU averages 40 - 50% at that point of excessive blocking

Any help would be greatly appreciated.

Thanks.Did the blocking happen with the user tables or in tempdb?|||It happens on the user tables... Thanks.|||This is probably a fault of the database application, not the server. Poorly designed schemas and bad code are the primary culprits of blocking, which may not occur until the application use scales beyond that which it was tested.

Try to figure out which sprocs or statements are causing the blocking, and then try to optimize them.|||Since your job actually finishes, and it did not happened at night. My guess would be:
1. Some process running long update transaction, or
2.|||2. Your IO time slows down due to workload, that may increases chance of blocking too.|||If you cannot spot abnormalties in profiler, have a look at IO time.|||Thanks for all your replies. It was pointing to an application issue. Thanks again.

Exception_Access_Violation.

I received the following AV in SS2K Std. Edition - I can find nothing on this
AV, however the log did fill with over 10,000 "WaitForChildren" errors before
we had to bring it down. I have the dump file and log file available, but this
looks like a pretty serious bug. If anyone from Microsoft can assist, please
reply. The stack dump is below. The mdmp file is available if needed. Please
reply to this newsgroup only.
This file is generated by Microsoft SQL Server 8.00.760
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Bios Version is DELL - 1
Phoenix ROM BIOS PLUS Version 1.10 A07
Current time is 13:12:17 11/05/04.
4 Intel x86 level 15, 2388 Mhz processor(s).
Windows NT 5.2 Build 3790 CSD .
Memory
MemoryLoad = 93%
Total Physical = 2047 MB
Available Physical = 143 MB
Total Page File = 3947 MB
Available Page File = 2266 MB
Total Virtual = 2047 MB
Available Virtual = 279 MB
*Stack Dump being sent to d:\MSSQL\log\SQLDump0001.txt
*
************************************************** ***************************
**
*
* BEGIN STACK DUMP:
* 11/05/04 13:12:17 spid 4
*
* Exception Address = 006FDA74 (CXid::Kill + 0000001E Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000004
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B2CFFF 0072d000
* ntdll 77F40000 77FF9FFF 000ba000
* kernel32 77E40000 77F33FFF 000f4000
* ADVAPI32 77DA0000 77E2FFFF 00090000
* RPCRT4 77C50000 77CF4FFF 000a5000
* USER32 77D00000 77D8EFFF 0008f000
* GDI32 77C00000 77C43FFF 00044000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 77BA0000 77BF3FFF 00054000
* UMS 41070000 4107CFFF 0000d000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 60020000 6002FFFF 00010000
* sqlevn70 41080000 41086FFF 00007000
* NETAPI32 02A00000 02A52FFF 00053000
* ole32 02E70000 02F94FFF 00125000
* XOLEHLP 02FA0000 02FA5FFF 00006000
* MSDTCPRX 02FB0000 03021FFF 00072000
* OLEAUT32 03030000 030ACFFF 0007d000
* MSVCP60 030B0000 03110FFF 00061000
* MTXCLU 03120000 03137FFF 00018000
* VERSION 03140000 03147FFF 00008000
* WSOCK32 03150000 03158FFF 00009000
* WS2_32 03160000 03177FFF 00018000
* WS2HELP 03180000 03187FFF 00008000
* CLUSAPI 031D0000 031E0FFF 00011000
* RESUTILS 031F0000 03201FFF 00012000
* USERENV 03210000 032C9FFF 000ba000
* MFC42u 032D0000 033C3FFF 000f4000
* secur32 03450000 03462FFF 00013000
* mswsock 03480000 034C2FFF 00043000
* DNSAPI 034D0000 034F6FFF 00027000
* winrnr 03540000 03546FFF 00007000
* WLDAP32 03550000 0357EFFF 0002f000
* rasadhlp 035A0000 035A4FFF 00005000
* SSNETLIB 00C70000 00C84FFF 00015000
* SSNMPN70 00C90000 00C95FFF 00006000
* security 04060000 04063FFF 00004000
* wshtcpip 040B0000 040B7FFF 00008000
* SSmsLPCn 040C0000 040C6FFF 00007000
* ntdsapi 040E0000 040F5FFF 00016000
* kerberos 04A50000 04AA5FFF 00056000
* cryptdll 04110000 0411BFFF 0000c000
* MSASN1 043B0000 043C1FFF 00012000
* rsaenh 0FFD0000 0FFFCFFF 0002d000
* PSAPI 04100000 0410AFFF 0000b000
* SQLFTQRY 048E0000 04911FFF 00032000
* CLBCatQ 055F0000 0566DFFF 0007e000
* COMRes 05670000 05735FFF 000c6000
* sqloledb 05740000 057C5FFF 00086000
* MSDART 057D0000 057F8FFF 00029000
* COMCTL32 05800000 0588FFFF 00090000
* comdlg32 05890000 058D6FFF 00047000
* SHLWAPI 058E0000 05928FFF 00049000
* SHELL32 05930000 0610CFFF 007dd000
* MSDATL3 06110000 06127FFF 00018000
* comctl32 06140000 06225FFF 000e6000
* oledb32 065D0000 0664CFFF 0007d000
* OLEDB32R 06650000 06660FFF 00011000
* xpsqlbot 06670000 06675FFF 00006000
* xpstar 06690000 066D6FFF 00047000
* SQLRESLD 066F0000 066F6FFF 00007000
* SQLSVC 06700000 06716FFF 00017000
* ODBC32 06720000 06759FFF 0003a000
* odbcbcp 06760000 06765FFF 00006000
* W95SCM 06770000 0677BFFF 0000c000
* SQLUNIRL 06780000 067ACFFF 0002d000
* WINSPOOL 067B0000 067D5FFF 00026000
* SHFOLDER 067E0000 067E8FFF 00009000
* odbcint 068F0000 06906FFF 00017000
* NDDEAPI 06AF0000 06AF6FFF 00007000
* SQLSVC 06B00000 06B05FFF 00006000
* xpstar 06B10000 06B18FFF 00009000
* xplog70 06BB0000 06BC1FFF 00012000
* xplog70 06680000 06683FFF 00004000
* odsole70 04000000 0400FFFF 00010000
* jmail 06E50000 06EA2FFF 00053000
* SAMLIB 03FE0000 03FEFFFF 00010000
* msv1_0 07530000 07553FFF 00024000
* ACTIVEDS 07590000 075C1FFF 00032000
* adsldpc 075D0000 075F5FFF 00026000
* credui 07600000 0762CFFF 0002d000
* ATL 07560000 07577FFF 00018000
* adsldp 07670000 0769CFFF 0002d000
* SXS 07820000 078D9FFF 000ba000
* DBNETLIB 00D50000 00D62FFF 00013000
* crypt32 070B0000 07147FFF 00098000
* msadce 050B0000 0510AFFF 0005b000
* msadcer 047B0000 047B4FFF 00005000
* srchadm 06FB0000 06FE5FFF 00036000
* mssws 04F00000 04F07FFF 00008000
* athprxy 04F10000 04F17FFF 00008000
* msi 09560000 09773FFF 00214000
* sqlmap70 044E0000 04509FFF 0002a000
* MAPI32 02E20000 02E3EFFF 0001f000
* dbghelp 07E60000 07F14FFF 000b5000
*
* Edi: 00000001:
* Esi: 19DC28A4: 009B694C 19DC2B88 19DDE0F0 00000002 00000003
00000
002
* Eax: 00000000:
* Ebx: 00000004:
* Ecx: 19DC28A4: 009B694C 19DC2B88 19DDE0F0 00000002 00000003
00000
002
* Edx: 7FFE0304: 0C819CC3 00010024 90C39D00 90909090 90909090
90909
090
* Eip: 006FDA74: 8904408B 4E8B7078 4DBCE848 C0850000 468B1475
08408
B3C
* Ebp: 03B6FDE8: 03B6FE30 008405DD 00000004 19D433B8 008404C7
2AB4C
098
* SegCs: 0000001B:
* EFlags: 00010246: 006F0072 00720067 006D0061 00460020 006C0069
00730
065
* Esp: 03B6FDD8: 2AB4C098 19D433B8 00000000 00000000 03B6FE30
00840
5DD
* SegSs: 00000023:
*
************************************************** ***************************
**
*
* Short Stack Dump
* 006FDA74 Module(sqlservr+002FDA74) (CXid::Kill+0000001E)
* 008405DD Module(sqlservr+004405DD)
(DeadlockMonitor::ResolveDeadlock+0000002A
)
* 008404C7 Module(sqlservr+004404C7)
(DeadlockMonitor::SearchAndResolve+0000013
4)
* 00516E1A Module(sqlservr+00116E1A) (lockMonitor+000002F7)
* 00516CC4 Module(sqlservr+00116CC4) (lockMonitorThread+000000A4)
* 41075002 Module(UMS+00005002) (ProcessWorkRequests+00000272)
* 41074698 Module(UMS+00004698) (ThreadStartRoutine+00000098)
* 77BC90A2 Module(MSVCRT+000290A2) (endthread+000000A6)
* 77E4A990 Module(kernel32+0000A990) (FlsSetValue+00000779)
* Location : 006FDA74 Module(sqlservr+002FDA74) (CXid::Kill+0000001E)
* Return Addr: 008405DD Module(sqlservr+004405DD)
(DeadlockMonitor::ResolveDead
lock+0000002A)
Frame : 03B6FDE8
Parameters:
[1] 00000004:
[2] 19D433B8: 00000012 000693CC 19DDE030 19D433C4 19D433C4 19DC3988
[3] 008404C7: 8901F883 850FEC45 FFBFD2B9 145005C7 000300A2 AAE90000
[4] 2AB4C098: 2F5BD1F0 2AB4C09C 2AB4C09C 2AB4C5E8 2AB4C5E8 2AB4C030
160 bytes of stack data from 03B6FD48 to 03B6FDE8
03B6FD48: 00000000 00000000 00000000 00000000 [............]
:::: 8 Duplicate lines detected
03B6FDD8: 2AB4C098 19D433B8 00000000 00000000 [...*.3........]
* Location : 008405DD Module(sqlservr+004405DD)
(DeadlockMonitor::ResolveDead
lock+0000002A)
* Return Addr: 008404C7 Module(sqlservr+004404C7)
(DeadlockMonitor::SearchAndRe
solve+00000134)
Frame : 03B6FDF4
Parameters:
[1] 2AB4C098: 2F5BD1F0 2AB4C09C 2AB4C09C 2AB4C5E8 2AB4C5E8 2AB4C030
[2] 19DC3988: 009B694C 19DDE130 19DDE130 00000002 00000001 00000001
[3] 19DC3988: 009B694C 19DDE130 19DDE130 00000002 00000001 00000001
[4] 03B6FE48: 00000000 00000000 00931CFA 00000000 00000000 2F5BD1F0
12 bytes of stack data from 03B6FDE8 to 03B6FDF4
03B6FDE8: 03B6FE30 008405DD 00000004 [0.........]
* Location : 008404C7 Module(sqlservr+004404C7)
(DeadlockMonitor::SearchAndRe
solve+00000134)
* Return Addr: 00516E1A Module(sqlservr+00116E1A) (lockMonitor+000002F7)
Frame : 03B6FE30
Parameters:
[1] 19DC3988: 009B694C 19DDE130 19DDE130 00000002 00000001 00000001
[2] 00C56780: 00C56780 00C56780 00000000 00000003 00000000 00000000
[3] 19DD6098: 01000004 00000000 00000000 00000000 00000000 00000000
[4] 00000001:
60 bytes of stack data from 03B6FDF4 to 03B6FE30
03B6FDF4: 19D433B8 008404C7 2AB4C098 19DC3988 [.3.......*.9..]
03B6FE04: 19DC3988 03B6FE48 00000000 00931CFA [.9..H.........]
03B6FE14: 02A65EDC 19D433B8 00000001 03B6FDFC [.^...3........]
03B6FE24: 03B6FEB4 009803BB 00000001 [.........]
* Location : 00516E1A Module(sqlservr+00116E1A) (lockMonitor+000002F7)
* Return Addr: 00516CC4 Module(sqlservr+00116CC4)
(lockMonitorThread+000000A4)
Frame : 03B6FEC0
Parameters:
[1] 00C56780: 00C56780 00C56780 00000000 00000003 00000000 00000000
[2] 0363503C: 00000000 00000000 03635010 00000000 00516C29 03B6FF1C
[3] 00000000:
[4] 77F43741: 90000CC2 90909090 00011AB8 0300BA00 D2FF7FFE 900004C2
144 bytes of stack data from 03B6FE30 to 03B6FEC0
03B6FE30: 03B6FEC0 00516E1A 19DC3988 00C56780 [....nQ..9...g..]
03B6FE40: 19DD6098 00000001 00000000 00000000 [.`...........]
03B6FE50: 00931CFA 00000000 00000000 2F5BD1F0 [...........[/]
03B6FE60: 2AB4C098 2F5BD44C 00000000 FFFFFFFF [...*L.[/......]
03B6FE70: 00000000 00000000 C1F7FA5B 2F5BD44C [......[...L.[/]
03B6FE80: 00000061 C1F8216B C1F7FA5B 00000065 [a...k!..[...e...]
03B6FE90: 00000000 2F5BD1F0 00000000 00000000 [.....[/......]
03B6FEA0: 2F5BD1F0 03B6FEEC 00001388 19DC3988 [..[/.......9..]
03B6FEB0: 03B6FE3C 03B6FEEC 0098061F 00000006 [<............]
* Location : 00516CC4 Module(sqlservr+00116CC4)
(lockMonitorThread+000000A4)
* Return Addr: 41075002 Module(UMS+00005002) (ProcessWorkRequests+00000272)
Frame : 03B6FEFC
Parameters:
[1] 00000000:
[2] 03635010: 41076104 00000000 00000000 00000000 00000000 C1F7FA5B
[3] 00000002:
[4] 00000000:
60 bytes of stack data from 03B6FEC0 to 03B6FEFC
03B6FEC0: 03B6FEFC 00516CC4 00C56780 0363503C [....lQ..g..<Pc.]
03B6FED0: 00000000 77F43741 77E41817 19DD6098 [...A7.w...w.`..]
03B6FEE0: 77E41864 03B6FEC8 77E4178F 03B6FF64 [d..w......wd...]
03B6FEF0: 00488754 009A7D90 00000000 [T.H..}.....]
* Location : 41075002 Module(UMS+00005002) (ProcessWorkRequests+00000272)
* Return Addr: 41074698 Module(UMS+00004698) (ThreadStartRoutine+00000098)
Frame : 03B6FF70
Parameters:
[1] 00C56788: 00000000 00000003 00000000 00000000 073AFF1C 0509FF1C
[2] 03635268: 0000078C 000002DC 00000009 00000000 00000000 3EBEC2C9
[3] 03B6FFB8: 03B6FFEC 77E4A990 03634D68 00000000 00000000 03634D68
[4] 03634D68: 048E76C0 048E7760 048E77A0 04900870 00000000 00000001
116 bytes of stack data from 03B6FEFC to 03B6FF70
03B6FEFC: 03B6FF70 41075002 00000000 03635010 [p...P.A....Pc.]
03B6FF0C: 00000002 00000000 00000001 00C56650 [.........Pf..]
03B6FF1C: 00000000 00000000 0000078C 03635080 [..........Pc.]
03B6FF2C: 00000000 00000000 00000002 03B6FF38 [.........8...]
03B6FF3C: 03B6FF38 00000000 00000002 00000000 [8............]
03B6FF4C: 41074A13 00000000 00000000 03635080 [.J.A.......Pc.]
03B6FF5C: 000DBBA0 00C56650 03B6FFA8 41075428 [...Pf.....(T.A]
03B6FF6C: 00000000 [...]
* Location : 41074698 Module(UMS+00004698) (ThreadStartRoutine+00000098)
* Return Addr: 77BC90A2 Module(MSVCRT+000290A2) (endthread+000000A6)
Frame : 03B6FF84
Parameters:
[1] 00C56780: 00C56780 00C56780 00000000 00000003 00000000 00000000
[2] 00000000:
[3] 00000000:
[4] 03634D68: 048E76C0 048E7760 048E77A0 04900870 00000000 00000001
20 bytes of stack data from 03B6FF70 to 03B6FF84
03B6FF70: 41074D90 41074698 00C56788 03635268 [.M.A.F.A.g..hRc.]
03B6FF80: 03B6FFB8 [...]
* Location : 77BC90A2 Module(MSVCRT+000290A2) (endthread+000000A6)
* Return Addr: 77E4A990 Module(kernel32+0000A990) (FlsSetValue+00000779)
Frame : 03B6FFB8
Parameters:
[1] 03634D68: 048E76C0 048E7760 048E77A0 04900870 00000000 00000001
[2] 00000000:
[3] 00000000:
[4] 03634D68: 048E76C0 048E7760 048E77A0 04900870 00000000 00000001
52 bytes of stack data from 03B6FF84 to 03B6FFB8
03B6FF84: 03634D68 77BC90A2 00C56780 00000000 [hMc...w.g.....]
03B6FF94: 00000000 03634D68 B6C14C90 03B6FF90 [...hMc..L.....]
03B6FFA4: 89450A30 03B6FFDC 77BC4D50 77BA4040 [0.E....PM.w@.@..w]
03B6FFB4: 00000000 [...]
* Location : 77E4A990 Module(kernel32+0000A990) (FlsSetValue+00000779)
* Return Addr: 00000000 Module(sqlservr+FFC00000)
Frame : 03B6FFEC
Parameters:
[1] 77BC9032: 40680C6A E877BA40 FFFFD3EE BAEC35FF 15FF77BE 77BED9FC
[2] 03634D68: 048E76C0 048E7760 048E77A0 04900870 00000000 00000001
[3] 00000000:
[4] 00000000:
*
PSS @.0x19DD6098
pspid = 4 m_dwLoginFlags = 0x0000 plsid = 433398744
pbackground = 1
pbSid
01 .
sSecFlags = 0x10
pdeadlockpri = 0 poffsets = 0x0 pss_stats = 0x0
ptickcount = 72093 pcputickcount = 169421353078
ploginstamp = 4 ptimestamp = 1900-01-01 00:00:00.000
plangid = 0 pdateformat = 0 pdatefirst = 0
poptions = 0x0 poptions2 = 0x0 pline = 0
pcurstepno = 0 prowcount = 0 pstatlist = 0
pcurcmd = 35 pseqstat = 0 ptextsize = 4096
pretstat = 0 CNestLevel = 0 @.@.procid = 0
pslastbatchstarttime = 1900-01-01 00:00:00.000 pmemusage = -174655
hLicense = 0 tpFlags = 0x1 isolation_level = 0
fips_flag = 0x0 sSaveSecFlags = 0x0 psavedb = 0
pfetchstat = 0 pcrsrows = 0
pslastbatchtime = 1900-01-01 00:00:00.000 pubexecdb = 0
fInReplicatedProcExec = 0 pMsqlXact = 0x19DD6A10 presSemCount =
[0]62322896
presSemCount = [0]62322896 pcputot = 0
pcputotstart = 0 pcpucmdstart = 0 pbufread = 0
pbufreadstart = 0 plogbufread = 0 plogbufreadstart = 0
pbufwrite = 0 pbufwritestart = 0 pLockTimeout =
4294967295
pUtilResultSet = 0x00000000
pec
ec_pss->pspid = 4 ecid = 0 ec_stat = 0x0
ec_stat2 = 0x100000 ec_atomic = 0x0 pcurdb = 0
ec_lasterror = 0 ec_preverror = 0 ec_cpucur = 0
ec_cmderrs = 0 ec_timeslice = 100 ec_dbtable = 0x00000000
ec_reswait = 0xa74488 ec_dbindex = -1 ec_waittype = 0x0000
Waittime = 0 ec_umsContext->m_pSched->m_id (SchedulerId) = 0x3
ec_umsContext->m_workercntxt->m_id (threadId) = 0x78c
ExecutionContext Summary @.0x19DD63C0
ec_pss->pspid = 4 ecid = 0 ec_stat = 0x0
ec_stat2 = 0x100000 ec_atomic = 0x0 pcurdb = 0
ec_lasterror = 0 ec_preverror = 0 ec_cpucur = 0
ec_cmderrs = 0 ec_timeslice = 100 ec_dbtable = 0x00000000
ec_reswait = 0xa74488 ec_dbindex = -1 ec_waittype = 0x0000
Waittime = 0 ec_umsContext->m_pSched->m_id (SchedulerId) = 0x3
ec_umsContext->m_workercntxt->m_id (threadId) = 0x78c
EC @.0x19DD63C0
ec_pss->pspid = 4 ecid = 0 ec_cmderrs = 0
ec_stat = 0x0 ec_stat2 = 0x100000 ec_atomic = 0x0
ec_curdb = 0 ec_dbtable = 0x0 ec_dbstat = 0x0
ec_lospin = 0x0 ec_dbindex = -1 ec_lasterror = 0
ec_save_ex_number = 0 ec_save_ex_address = 0x00000000
ec_cpucur = 0 ec_timeslice = 100 ec_next = 0x00000000
ec_prev = 0x00000000 ec_state = 1 ec_waittype = 0x0000
ec_dbstat2 = 0 ec_preverror = 0 ec_subecstat = 1
ec_subecnext = 0x00000000 ec_resSemWaitInfo = 0
ec_umsContext->m_pSched->m_id (SchedulerId) = 0x3
ec_umsContext->m_workercntxt->m_id (threadId) = 0x78c
ec_backupContext = 0x00000000 ec_dbccContext =
0x00000000
ec_allocFile = 0x00000000 ec_buftrace.Index = 0
EXCEPT (null) @.0x03B6FE08
exc_number = 0 exc_severity = 0
exc_func = hdl_prntbackout+0 Line 0+0x0
Hi
Similar bug, but that was fixed in 8.00.0592.
Try applying 8.00.0878
(http://support.microsoft.com/default...b;en-us;838166), failing
that, open a ticket with Microsoft PSS
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"VEN" <VEN@.discussions.microsoft.com> wrote in message
news:8EDA3346-D12E-4D9C-B8A4-DBD4E6A328A5@.microsoft.com...
> I received the following AV in SS2K Std. Edition - I can find nothing on
this
> AV, however the log did fill with over 10,000 "WaitForChildren" errors
before
> we had to bring it down. I have the dump file and log file available, but
this
> looks like a pretty serious bug. If anyone from Microsoft can assist,
please
> reply. The stack dump is below. The mdmp file is available if needed.
Please
> reply to this newsgroup only.
> This file is generated by Microsoft SQL Server 8.00.760
> upon detection of fatal unexpected error. Please return this file,
> the query or program that produced the bugcheck, the database and
> the error log, and any other pertinent information with a Service Request.
>
> Computer type is AT/AT COMPATIBLE.
> Bios Version is DELL - 1
> Phoenix ROM BIOS PLUS Version 1.10 A07
> Current time is 13:12:17 11/05/04.
> 4 Intel x86 level 15, 2388 Mhz processor(s).
> Windows NT 5.2 Build 3790 CSD .
>
> Memory
> MemoryLoad = 93%
> Total Physical = 2047 MB
> Available Physical = 143 MB
> Total Page File = 3947 MB
> Available Page File = 2266 MB
> Total Virtual = 2047 MB
> Available Virtual = 279 MB
> *Stack Dump being sent to d:\MSSQL\log\SQLDump0001.txt
> *
>
************************************************** **************************
*
> **
> *
> * BEGIN STACK DUMP:
> * 11/05/04 13:12:17 spid 4
> *
> * Exception Address = 006FDA74 (CXid::Kill + 0000001E Line 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000004
> *
> *
> * MODULE BASE END SIZE
> * sqlservr 00400000 00B2CFFF 0072d000
> * ntdll 77F40000 77FF9FFF 000ba000
> * kernel32 77E40000 77F33FFF 000f4000
> * ADVAPI32 77DA0000 77E2FFFF 00090000
> * RPCRT4 77C50000 77CF4FFF 000a5000
> * USER32 77D00000 77D8EFFF 0008f000
> * GDI32 77C00000 77C43FFF 00044000

> * OPENDS60 41060000 41065FFF 00006000
> * MSVCRT 77BA0000 77BF3FFF 00054000
> * UMS 41070000 4107CFFF 0000d000
> * SQLSORT 42AE0000 42B6FFFF 00090000
> * MSVCIRT 60020000 6002FFFF 00010000
> * sqlevn70 41080000 41086FFF 00007000
> * NETAPI32 02A00000 02A52FFF 00053000
> * ole32 02E70000 02F94FFF 00125000
> * XOLEHLP 02FA0000 02FA5FFF 00006000
> * MSDTCPRX 02FB0000 03021FFF 00072000
> * OLEAUT32 03030000 030ACFFF 0007d000
> * MSVCP60 030B0000 03110FFF 00061000
> * MTXCLU 03120000 03137FFF 00018000
> * VERSION 03140000 03147FFF 00008000
> * WSOCK32 03150000 03158FFF 00009000
> * WS2_32 03160000 03177FFF 00018000
> * WS2HELP 03180000 03187FFF 00008000
> * CLUSAPI 031D0000 031E0FFF 00011000
> * RESUTILS 031F0000 03201FFF 00012000
> * USERENV 03210000 032C9FFF 000ba000
> * MFC42u 032D0000 033C3FFF 000f4000
> * secur32 03450000 03462FFF 00013000
> * mswsock 03480000 034C2FFF 00043000
> * DNSAPI 034D0000 034F6FFF 00027000
> * winrnr 03540000 03546FFF 00007000
> * WLDAP32 03550000 0357EFFF 0002f000
> * rasadhlp 035A0000 035A4FFF 00005000
> * SSNETLIB 00C70000 00C84FFF 00015000
> * SSNMPN70 00C90000 00C95FFF 00006000
> * security 04060000 04063FFF 00004000
> * wshtcpip 040B0000 040B7FFF 00008000
> * SSmsLPCn 040C0000 040C6FFF 00007000
> * ntdsapi 040E0000 040F5FFF 00016000
> * kerberos 04A50000 04AA5FFF 00056000
> * cryptdll 04110000 0411BFFF 0000c000
> * MSASN1 043B0000 043C1FFF 00012000
> * rsaenh 0FFD0000 0FFFCFFF 0002d000
> * PSAPI 04100000 0410AFFF 0000b000
> * SQLFTQRY 048E0000 04911FFF 00032000
> * CLBCatQ 055F0000 0566DFFF 0007e000
> * COMRes 05670000 05735FFF 000c6000
> * sqloledb 05740000 057C5FFF 00086000
> * MSDART 057D0000 057F8FFF 00029000
> * COMCTL32 05800000 0588FFFF 00090000
> * comdlg32 05890000 058D6FFF 00047000
> * SHLWAPI 058E0000 05928FFF 00049000
> * SHELL32 05930000 0610CFFF 007dd000
> * MSDATL3 06110000 06127FFF 00018000
> * comctl32 06140000 06225FFF 000e6000
> * oledb32 065D0000 0664CFFF 0007d000
> * OLEDB32R 06650000 06660FFF 00011000
> * xpsqlbot 06670000 06675FFF 00006000
> * xpstar 06690000 066D6FFF 00047000
> * SQLRESLD 066F0000 066F6FFF 00007000
> * SQLSVC 06700000 06716FFF 00017000
> * ODBC32 06720000 06759FFF 0003a000
> * odbcbcp 06760000 06765FFF 00006000
> * W95SCM 06770000 0677BFFF 0000c000
> * SQLUNIRL 06780000 067ACFFF 0002d000
> * WINSPOOL 067B0000 067D5FFF 00026000
> * SHFOLDER 067E0000 067E8FFF 00009000
> * odbcint 068F0000 06906FFF 00017000
> * NDDEAPI 06AF0000 06AF6FFF 00007000
> * SQLSVC 06B00000 06B05FFF 00006000
> * xpstar 06B10000 06B18FFF 00009000
> * xplog70 06BB0000 06BC1FFF 00012000
> * xplog70 06680000 06683FFF 00004000
> * odsole70 04000000 0400FFFF 00010000
> * jmail 06E50000 06EA2FFF 00053000
> * SAMLIB 03FE0000 03FEFFFF 00010000
> * msv1_0 07530000 07553FFF 00024000
> * ACTIVEDS 07590000 075C1FFF 00032000
> * adsldpc 075D0000 075F5FFF 00026000
> * credui 07600000 0762CFFF 0002d000
> * ATL 07560000 07577FFF 00018000
> * adsldp 07670000 0769CFFF 0002d000
> * SXS 07820000 078D9FFF 000ba000
> * DBNETLIB 00D50000 00D62FFF 00013000
> * crypt32 070B0000 07147FFF 00098000
> * msadce 050B0000 0510AFFF 0005b000
> * msadcer 047B0000 047B4FFF 00005000
> * srchadm 06FB0000 06FE5FFF 00036000
> * mssws 04F00000 04F07FFF 00008000
> * athprxy 04F10000 04F17FFF 00008000
> * msi 09560000 09773FFF 00214000
> * sqlmap70 044E0000 04509FFF 0002a000
> * MAPI32 02E20000 02E3EFFF 0001f000
> * dbghelp 07E60000 07F14FFF 000b5000
> *
> * Edi: 00000001:
> * Esi: 19DC28A4: 009B694C 19DC2B88 19DDE0F0 00000002 00000003
> 00000
> 002
> * Eax: 00000000:
> * Ebx: 00000004:
> * Ecx: 19DC28A4: 009B694C 19DC2B88 19DDE0F0 00000002 00000003
> 00000
> 002
> * Edx: 7FFE0304: 0C819CC3 00010024 90C39D00 90909090 90909090
> 90909
> 090
> * Eip: 006FDA74: 8904408B 4E8B7078 4DBCE848 C0850000 468B1475
> 08408
> B3C
> * Ebp: 03B6FDE8: 03B6FE30 008405DD 00000004 19D433B8 008404C7
> 2AB4C
> 098
> * SegCs: 0000001B:
> * EFlags: 00010246: 006F0072 00720067 006D0061 00460020 006C0069
> 00730
> 065
> * Esp: 03B6FDD8: 2AB4C098 19D433B8 00000000 00000000 03B6FE30
> 00840
> 5DD
> * SegSs: 00000023:
> *
>
************************************************** **************************
*
> **
> *
> ----
--
> --
> * Short Stack Dump
> * 006FDA74 Module(sqlservr+002FDA74) (CXid::Kill+0000001E)
> * 008405DD Module(sqlservr+004405DD)
> (DeadlockMonitor::ResolveDeadlock+0000002A
> )
> * 008404C7 Module(sqlservr+004404C7)
> (DeadlockMonitor::SearchAndResolve+0000013
> 4)
> * 00516E1A Module(sqlservr+00116E1A) (lockMonitor+000002F7)
> * 00516CC4 Module(sqlservr+00116CC4) (lockMonitorThread+000000A4)
> * 41075002 Module(UMS+00005002) (ProcessWorkRequests+00000272)
> * 41074698 Module(UMS+00004698) (ThreadStartRoutine+00000098)
> * 77BC90A2 Module(MSVCRT+000290A2) (endthread+000000A6)
> * 77E4A990 Module(kernel32+0000A990) (FlsSetValue+00000779)
> ----
--
> * Location : 006FDA74 Module(sqlservr+002FDA74) (CXid::Kill+0000001E)
> * Return Addr: 008405DD Module(sqlservr+004405DD)
> (DeadlockMonitor::ResolveDead
> lock+0000002A)
> Frame : 03B6FDE8
> Parameters:
> [1] 00000004:
> [2] 19D433B8: 00000012 000693CC 19DDE030 19D433C4 19D433C4 19DC3988
> [3] 008404C7: 8901F883 850FEC45 FFBFD2B9 145005C7 000300A2 AAE90000
> [4] 2AB4C098: 2F5BD1F0 2AB4C09C 2AB4C09C 2AB4C5E8 2AB4C5E8 2AB4C030
> 160 bytes of stack data from 03B6FD48 to 03B6FDE8
> 03B6FD48: 00000000 00000000 00000000 00000000 [............]
> :::: 8 Duplicate lines detected
> 03B6FDD8: 2AB4C098 19D433B8 00000000 00000000 [...*.3........]
>
> ----
--
> * Location : 008405DD Module(sqlservr+004405DD)
> (DeadlockMonitor::ResolveDead
> lock+0000002A)
> * Return Addr: 008404C7 Module(sqlservr+004404C7)
> (DeadlockMonitor::SearchAndRe
> solve+00000134)
> Frame : 03B6FDF4
> Parameters:
> [1] 2AB4C098: 2F5BD1F0 2AB4C09C 2AB4C09C 2AB4C5E8 2AB4C5E8 2AB4C030
> [2] 19DC3988: 009B694C 19DDE130 19DDE130 00000002 00000001 00000001
> [3] 19DC3988: 009B694C 19DDE130 19DDE130 00000002 00000001 00000001
> [4] 03B6FE48: 00000000 00000000 00931CFA 00000000 00000000 2F5BD1F0
> 12 bytes of stack data from 03B6FDE8 to 03B6FDF4
> 03B6FDE8: 03B6FE30 008405DD 00000004 [0.........]
>
> ----
--
> * Location : 008404C7 Module(sqlservr+004404C7)
> (DeadlockMonitor::SearchAndRe
> solve+00000134)
> * Return Addr: 00516E1A Module(sqlservr+00116E1A) (lockMonitor+000002F7)
> Frame : 03B6FE30
> Parameters:
> [1] 19DC3988: 009B694C 19DDE130 19DDE130 00000002 00000001 00000001
> [2] 00C56780: 00C56780 00C56780 00000000 00000003 00000000 00000000
> [3] 19DD6098: 01000004 00000000 00000000 00000000 00000000 00000000
> [4] 00000001:
> 60 bytes of stack data from 03B6FDF4 to 03B6FE30
> 03B6FDF4: 19D433B8 008404C7 2AB4C098 19DC3988 [.3.......*.9..]
> 03B6FE04: 19DC3988 03B6FE48 00000000 00931CFA [.9..H.........]
> 03B6FE14: 02A65EDC 19D433B8 00000001 03B6FDFC [.^...3........]
> 03B6FE24: 03B6FEB4 009803BB 00000001 [.........]
>
> ----
--
> * Location : 00516E1A Module(sqlservr+00116E1A) (lockMonitor+000002F7)
> * Return Addr: 00516CC4 Module(sqlservr+00116CC4)
> (lockMonitorThread+000000A4)
> Frame : 03B6FEC0
> Parameters:
> [1] 00C56780: 00C56780 00C56780 00000000 00000003 00000000 00000000
> [2] 0363503C: 00000000 00000000 03635010 00000000 00516C29 03B6FF1C
> [3] 00000000:
> [4] 77F43741: 90000CC2 90909090 00011AB8 0300BA00 D2FF7FFE 900004C2
> 144 bytes of stack data from 03B6FE30 to 03B6FEC0
> 03B6FE30: 03B6FEC0 00516E1A 19DC3988 00C56780 [....nQ..9...g..]
> 03B6FE40: 19DD6098 00000001 00000000 00000000 [.`...........]
> 03B6FE50: 00931CFA 00000000 00000000 2F5BD1F0 [...........[/]
> 03B6FE60: 2AB4C098 2F5BD44C 00000000 FFFFFFFF [...*L.[/......]
> 03B6FE70: 00000000 00000000 C1F7FA5B 2F5BD44C [......[...L.[/]
> 03B6FE80: 00000061 C1F8216B C1F7FA5B 00000065 [a...k!..[...e...]
> 03B6FE90: 00000000 2F5BD1F0 00000000 00000000 [.....[/......]
> 03B6FEA0: 2F5BD1F0 03B6FEEC 00001388 19DC3988 [..[/.......9..]
> 03B6FEB0: 03B6FE3C 03B6FEEC 0098061F 00000006 [<............]
>
> ----
--
> * Location : 00516CC4 Module(sqlservr+00116CC4)
> (lockMonitorThread+000000A4)
> * Return Addr: 41075002 Module(UMS+00005002)
(ProcessWorkRequests+00000272)
> Frame : 03B6FEFC
> Parameters:
> [1] 00000000:
> [2] 03635010: 41076104 00000000 00000000 00000000 00000000 C1F7FA5B
> [3] 00000002:
> [4] 00000000:
> 60 bytes of stack data from 03B6FEC0 to 03B6FEFC
> 03B6FEC0: 03B6FEFC 00516CC4 00C56780 0363503C [....lQ..g..<Pc.]
> 03B6FED0: 00000000 77F43741 77E41817 19DD6098 [...A7.w...w.`..]
> 03B6FEE0: 77E41864 03B6FEC8 77E4178F 03B6FF64 [d..w......wd...]
> 03B6FEF0: 00488754 009A7D90 00000000 [T.H..}.....]
>
> ----
--
> * Location : 41075002 Module(UMS+00005002)
(ProcessWorkRequests+00000272)
> * Return Addr: 41074698 Module(UMS+00004698) (ThreadStartRoutine+00000098)
> Frame : 03B6FF70
> Parameters:
> [1] 00C56788: 00000000 00000003 00000000 00000000 073AFF1C 0509FF1C
> [2] 03635268: 0000078C 000002DC 00000009 00000000 00000000 3EBEC2C9
> [3] 03B6FFB8: 03B6FFEC 77E4A990 03634D68 00000000 00000000 03634D68
> [4] 03634D68: 048E76C0 048E7760 048E77A0 04900870 00000000 00000001
> 116 bytes of stack data from 03B6FEFC to 03B6FF70
> 03B6FEFC: 03B6FF70 41075002 00000000 03635010 [p...P.A....Pc.]
> 03B6FF0C: 00000002 00000000 00000001 00C56650 [.........Pf..]
> 03B6FF1C: 00000000 00000000 0000078C 03635080 [..........Pc.]
> 03B6FF2C: 00000000 00000000 00000002 03B6FF38 [.........8...]
> 03B6FF3C: 03B6FF38 00000000 00000002 00000000 [8............]
> 03B6FF4C: 41074A13 00000000 00000000 03635080 [.J.A.......Pc.]
> 03B6FF5C: 000DBBA0 00C56650 03B6FFA8 41075428 [...Pf.....(T.A]
> 03B6FF6C: 00000000 [...]
>
> ----
--
> * Location : 41074698 Module(UMS+00004698) (ThreadStartRoutine+00000098)
> * Return Addr: 77BC90A2 Module(MSVCRT+000290A2) (endthread+000000A6)
> Frame : 03B6FF84
> Parameters:
> [1] 00C56780: 00C56780 00C56780 00000000 00000003 00000000 00000000
> [2] 00000000:
> [3] 00000000:
> [4] 03634D68: 048E76C0 048E7760 048E77A0 04900870 00000000 00000001
> 20 bytes of stack data from 03B6FF70 to 03B6FF84
> 03B6FF70: 41074D90 41074698 00C56788 03635268 [.M.A.F.A.g..hRc.]
> 03B6FF80: 03B6FFB8 [...]
>
> ----
--
> * Location : 77BC90A2 Module(MSVCRT+000290A2) (endthread+000000A6)
> * Return Addr: 77E4A990 Module(kernel32+0000A990) (FlsSetValue+00000779)
> Frame : 03B6FFB8
> Parameters:
> [1] 03634D68: 048E76C0 048E7760 048E77A0 04900870 00000000 00000001
> [2] 00000000:
> [3] 00000000:
> [4] 03634D68: 048E76C0 048E7760 048E77A0 04900870 00000000 00000001
> 52 bytes of stack data from 03B6FF84 to 03B6FFB8
> 03B6FF84: 03634D68 77BC90A2 00C56780 00000000 [hMc...w.g.....]
> 03B6FF94: 00000000 03634D68 B6C14C90 03B6FF90 [...hMc..L.....]
> 03B6FFA4: 89450A30 03B6FFDC 77BC4D50 77BA4040 [0.E....PM.w@.@..w]
> 03B6FFB4: 00000000 [...]
>
> ----
--
> * Location : 77E4A990 Module(kernel32+0000A990) (FlsSetValue+00000779)
> * Return Addr: 00000000 Module(sqlservr+FFC00000)
> Frame : 03B6FFEC
> Parameters:
> [1] 77BC9032: 40680C6A E877BA40 FFFFD3EE BAEC35FF 15FF77BE 77BED9FC
> [2] 03634D68: 048E76C0 048E7760 048E77A0 04900870 00000000 00000001
> [3] 00000000:
> [4] 00000000:
> *
> ----
--
> --
> PSS @.0x19DD6098
> --
> pspid = 4 m_dwLoginFlags = 0x0000 plsid = 433398744
> pbackground = 1
> pbSid
> --
> 01 .
> sSecFlags = 0x10
> pdeadlockpri = 0 poffsets = 0x0 pss_stats = 0x0
> ptickcount = 72093 pcputickcount = 169421353078
> ploginstamp = 4 ptimestamp = 1900-01-01 00:00:00.000
> plangid = 0 pdateformat = 0 pdatefirst = 0
> poptions = 0x0 poptions2 = 0x0 pline = 0
> pcurstepno = 0 prowcount = 0 pstatlist = 0
> pcurcmd = 35 pseqstat = 0 ptextsize = 4096
> pretstat = 0 CNestLevel = 0 @.@.procid = 0
> pslastbatchstarttime = 1900-01-01 00:00:00.000 pmemusage = -174655
> hLicense = 0 tpFlags = 0x1 isolation_level = 0
> fips_flag = 0x0 sSaveSecFlags = 0x0 psavedb = 0
> pfetchstat = 0 pcrsrows = 0
> pslastbatchtime = 1900-01-01 00:00:00.000 pubexecdb = 0
> fInReplicatedProcExec = 0 pMsqlXact = 0x19DD6A10 presSemCount =
> [0]62322896
> presSemCount = [0]62322896 pcputot = 0
> pcputotstart = 0 pcpucmdstart = 0 pbufread = 0
> pbufreadstart = 0 plogbufread = 0 plogbufreadstart = 0
> pbufwrite = 0 pbufwritestart = 0 pLockTimeout =
> 4294967295
> pUtilResultSet = 0x00000000
> pec
> --
> ec_pss->pspid = 4 ecid = 0 ec_stat = 0x0
> ec_stat2 = 0x100000 ec_atomic = 0x0 pcurdb = 0
> ec_lasterror = 0 ec_preverror = 0 ec_cpucur = 0
> ec_cmderrs = 0 ec_timeslice = 100 ec_dbtable =
0x00000000
> ec_reswait = 0xa74488 ec_dbindex = -1 ec_waittype = 0x0000
> Waittime = 0 ec_umsContext->m_pSched->m_id (SchedulerId) =
0x3
> ec_umsContext->m_workercntxt->m_id (threadId) = 0x78c
>
>
> ExecutionContext Summary @.0x19DD63C0
> --
> ec_pss->pspid = 4 ecid = 0 ec_stat = 0x0
> ec_stat2 = 0x100000 ec_atomic = 0x0 pcurdb = 0
> ec_lasterror = 0 ec_preverror = 0 ec_cpucur = 0
> ec_cmderrs = 0 ec_timeslice = 100 ec_dbtable =
0x00000000
> ec_reswait = 0xa74488 ec_dbindex = -1 ec_waittype = 0x0000
> Waittime = 0 ec_umsContext->m_pSched->m_id (SchedulerId) =
0x3
> ec_umsContext->m_workercntxt->m_id (threadId) = 0x78c
>
>
> EC @.0x19DD63C0
> --
> ec_pss->pspid = 4 ecid = 0 ec_cmderrs = 0
> ec_stat = 0x0 ec_stat2 = 0x100000 ec_atomic = 0x0
> ec_curdb = 0 ec_dbtable = 0x0 ec_dbstat = 0x0
> ec_lospin = 0x0 ec_dbindex = -1 ec_lasterror = 0
> ec_save_ex_number = 0 ec_save_ex_address = 0x00000000
> ec_cpucur = 0 ec_timeslice = 100 ec_next = 0x00000000
> ec_prev = 0x00000000 ec_state = 1 ec_waittype = 0x0000
> ec_dbstat2 = 0 ec_preverror = 0 ec_subecstat = 1
> ec_subecnext = 0x00000000 ec_resSemWaitInfo = 0
> ec_umsContext->m_pSched->m_id (SchedulerId) = 0x3
> ec_umsContext->m_workercntxt->m_id (threadId) = 0x78c
> ec_backupContext = 0x00000000 ec_dbccContext =
> 0x00000000
> ec_allocFile = 0x00000000 ec_buftrace.Index = 0
> EXCEPT (null) @.0x03B6FE08
> --
> exc_number = 0 exc_severity = 0
> exc_func = hdl_prntbackout+0 Line 0+0x0
>
>