about accessing SQL Server2005 database file from a remote computer

hi every one. i am a new user of asp.net 2.0 using C# code and i am facing a problem in accessing a SQL Server2005 database file in the remote computer. i have connected two pc with peer to peer connection and trying to add a databse using the "Add connection" option from the visual studio 2005. in the add connection dialog box it is showing me the remote server and it was supposed to show all the database in that SQL Server when i select one. but when i am choosing the server name it was not showing me anything. by the way i have configuered the surface area for "both TCP/IP and named pipes" and both the pc's server browser is turned on. is it the right way to access a database file from a remote pc or not?? please send me a good solution to do this things and try to explain the codes with example. waiting for response...plz send me the solution.. as soon as possible

Hi,

Try the following KB article, it may be helpful to you.

http://support.microsoft.com/kb/316649

Thanks.

|||

Hi,

SQL Server 2005 is not allowing remote connections by default. You have to configure the SQL Server 2005 for remote connections using SQL Server Surface Area Configuration tool.

If you refer to article athttp://www.kodyaz.com/content/SQLServerdoesnotallowremoteconnections.aspx , you may see how you can use this tool for allowing remote connections for a sql server instance.

Eralper

sql

Abnormal user connection increased on SQL server

The error which we are facing these days is related to the abnormal
performance of SQL server, and sometimes also got the error ‘SQL Run-time
error 2147217911(80040e09)’ sometimes on the client ends.
As per our detailed observation it is observed that our SQL server database
response sometimes get dead slow and that is why all the related application
and website responses gets slow as well.
This delay of responses is not always but rarely and the unusual things
noticed are that user connection on the SQL server and on our web server
increases dynamically, which really seems to be faking not actual. And when
these user connections drops all the SQL related activities are back to
normal. Just to add more we are using windows 2003 server enterprise edition
of 64bit and SQL server 2000 enterprise edition of 64bit as well.”
Memory utilisation is also increased i.e. used more than normal.
"JS" wrote:

> The error which we are facing these days is related to the abnormal
> performance of SQL server, and sometimes also got the error ‘SQL Run-time
> error 2147217911(80040e09)’ sometimes on the client ends.
> As per our detailed observation it is observed that our SQL server database
> response sometimes get dead slow and that is why all the related application
> and website responses gets slow as well.
> This delay of responses is not always but rarely and the unusual things
> noticed are that user connection on the SQL server and on our web server
> increases dynamically, which really seems to be faking not actual. And when
> these user connections drops all the SQL related activities are back to
> normal. Just to add more we are using windows 2003 server enterprise edition
> of 64bit and SQL server 2000 enterprise edition of 64bit as well.”
>
>
>

Abnormal user connection increased on SQL server

The error which we are facing these days is related to the abnormal
performance of SQL server, and sometimes also got the error ‘SQL Run-time
error 2147217911(80040e09)’ sometimes on the client ends.
As per our detailed observation it is observed that our SQL server database
response sometimes get dead slow and that is why all the related application
and website responses gets slow as well.
This delay of responses is not always but rarely and the unusual things
noticed are that user connection on the SQL server and on our web server
increases dynamically, which really seems to be faking not actual. And when
these user connections drops all the SQL related activities are back to
normal. Just to add more we are using windows 2003 server enterprise edition
of 64bit and SQL server 2000 enterprise edition of 64bit as well.”Memory utilisation is also increased i.e. used more than normal.
"JS" wrote:

> The error which we are facing these days is related to the abnormal
> performance of SQL server, and sometimes also got the error ‘SQL Run-tim
e
> error 2147217911(80040e09)’ sometimes on the client ends.
> As per our detailed observation it is observed that our SQL server databas
e
> response sometimes get dead slow and that is why all the related applicati
on
> and website responses gets slow as well.
> This delay of responses is not always but rarely and the unusual things
> noticed are that user connection on the SQL server and on our web server
> increases dynamically, which really seems to be faking not actual. And whe
n
> these user connections drops all the SQL related activities are back to
> normal. Just to add more we are using windows 2003 server enterprise editi
on
> of 64bit and SQL server 2000 enterprise edition of 64bit as well.”
>
>
>

Abnormal user connection increased on SQL server

The error which we are facing these days is related to the abnormal
performance of SQL server, and sometimes also got the error â'SQL Run-time
error 2147217911(80040e09)â' sometimes on the client ends.
As per our detailed observation it is observed that our SQL server database
response sometimes get dead slow and that is why all the related application
and website responses gets slow as well.
This delay of responses is not always but rarely and the unusual things
noticed are that user connection on the SQL server and on our web server
increases dynamically, which really seems to be faking not actual. And when
these user connections drops all the SQL related activities are back to
normal. Just to add more we are using windows 2003 server enterprise edition
of 64bit and SQL server 2000 enterprise edition of 64bit as well.â'Memory utilisation is also increased i.e. used more than normal.
"JS" wrote:
> The error which we are facing these days is related to the abnormal
> performance of SQL server, and sometimes also got the error â'SQL Run-time
> error 2147217911(80040e09)â' sometimes on the client ends.
> As per our detailed observation it is observed that our SQL server database
> response sometimes get dead slow and that is why all the related application
> and website responses gets slow as well.
> This delay of responses is not always but rarely and the unusual things
> noticed are that user connection on the SQL server and on our web server
> increases dynamically, which really seems to be faking not actual. And when
> these user connections drops all the SQL related activities are back to
> normal. Just to add more we are using windows 2003 server enterprise edition
> of 64bit and SQL server 2000 enterprise edition of 64bit as well.â'
>
>
>sql

A way to add .Net classes in a report?

I know that subject line might be a little confusing - -
I have a project with a .vb class, that, when accessing a web page, based on
the user's login, I grab their Employee ID. With that, I can conceivably
create a method to get a list of employees who report to that person
Is there a way to include a .vb class in a report project, and then, access
a particular method (in this case, getting the list of direct reports), in
the document map?Elmo,
I have done something similar. You should be able to create a reportviewer
in an aspx page and access your class in the aspx code...pass the subsequent
values to the report.
billN
--
Message posted via http://www.sqlmonster.com|||Will I be able to use the same report file (.rdl) that I designed in a
Report Server Project in BI?
I tried adding a .rdl file to a ReportViewer control one time, and it wasn't
recognized.
"wnichols via SQLMonster.com" <u3357@.uwe> wrote in message
news:7e587ee1771a4@.uwe...
> Elmo,
> I have done something similar. You should be able to create a
> reportviewer
> in an aspx page and access your class in the aspx code...pass the
> subsequent
> values to the report.
> billN
> --
> Message posted via http://www.sqlmonster.com
>

A User connections question

Is there anyway or SQL script to configure the SQL server to automatically
log users out if their connection remains idle for a predetermined period of
time?
The convenience of allowing users to login to Great Plains in the morning
and staying there all day, even if they actually use the application for only
a fraction of the time, is too expensive to cater to.
I want to start by demanding that users logout of Great Plains when they
will not be using the application for a period of time (i.e. during lunch,
during coffee breaks, during meetings, while they are performing tasks not
requiring immediate access to Great Plains, etc.).
Thank You...
Hi
This is one of the things connection pooling would help with, but then you
will not be re-writting the package! I guess you could have a job that checks
the last_batch time in sysprocesses.
John
"clairvoyant316" wrote:

> Is there anyway or SQL script to configure the SQL server to automatically
> log users out if their connection remains idle for a predetermined period of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for only
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
|||clairvoyant316 wrote:
> Is there anyway or SQL script to configure the SQL server to automatically
> log users out if their connection remains idle for a predetermined period of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for only
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
Tried to post this yesterday, apparently it didn't "go". Here's a
script (and table) that I've used to handle this very problem in GP.
CREATE TABLE [dbo].[sb_IdleUsersRemoved] (
[iRowID] [int] IDENTITY (1, 1) NOT NULL ,
[iSPID] [int] NULL ,
[vchUserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[dtDateRemoved] [datetime] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE sb_RemoveIdleUsers
AS
DECLARE @.iSPID INT
DECLARE @.vchCommand VARCHAR(255)
/* Create list of SPIDs that have been idle for 12 hours, and are still
listed in dex_session */
SELECT a.spid, a.loginame
INTO #tempSPIDs
FROM master.dbo.sysprocesses a
INNER JOIN tempdb.dbo.dex_session b
ON a.spid = b.sqlsvr_spid
WHERE GETDATE() - a.last_batch > .5
UNION
SELECT spid, loginame
FROM master.dbo.sysprocesses
WHERE dbid IN (7, 16, 17) AND GETDATE() - last_batch > .5
BEGIN TRANSACTION
/* Record the list of spids and logins that are being removed */
INSERT INTO sb_custom.dbo.sb_IdleUsersRemoved
SELECT spid, loginame, GETDATE()
FROM #tempSPIDs
/* Delete records from dex_lock for any idle sessions */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id FROM tempdb.dbo.dex_session WHERE
sqlsvr_spid IN (SELECT spid FROM #tempSPIDs))
/* Delete records from dex_session for any idle sessions */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid IN (SELECT spid FROM #tempSPIDs)
COMMIT TRANSACTION
/* Kill SQL processes for idle sessions */
SELECT @.iSPID = 0
WHILE EXISTS(SELECT * FROM #tempSPIDs WHERE spid > @.iSPID)
BEGIN
SELECT TOP 1 @.iSPID = spid
FROM #tempSPIDs
WHERE spid > @.iSPID
ORDER BY spid
SELECT @.vchCommand = 'KILL ' + LTRIM(RTRIM(CONVERT(VARCHAR(10),
@.iSPID)))
EXEC (@.vchCommand)
END
DROP TABLE #tempSPIDs
/* Delete records from dex_lock for any records in dex_session that have
no active SQL connection */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
)
/* Delete records from dex_session that have no active SQL connection */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
GO
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks for your prompt response.
I ran that script and crashed the MS SQL 2000 with timeout as well as
corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
It didn't kick users out of the Great Plains at all.
Is there any condition or special configuration that I am not aware of to
run that script?
Thank You
"Tracy McKibben" wrote:

> Tried to post this yesterday, apparently it didn't "go". Here's a
> script (and table) that I've used to handle this very problem in GP.
|||clairvoyant316 wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
Not likely - the script I sent you simply creates a table and a stored
procedure.

> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
>
The script that I gave you creates a stored procedure. YOU must run
that stored procedure before it will remove any users. Surely you
realized that when you reviewed the script? Sounds like you might be
better off just leaving things alone.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi
tempdb is recreated when you restart SQL Server. If it was really corrupted
I would suspect a hardware failure rather than this script. There seems to be
a couple of table creation statements missing from the script and the
database names need changing on the four part names that are not tempdb.
John
"clairvoyant316" wrote:

> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
> Thank You
> "Tracy McKibben" wrote:
>
|||John Bell wrote:
> Hi
> tempdb is recreated when you restart SQL Server. If it was really corrupted
> I would suspect a hardware failure rather than this script. There seems to be
> a couple of table creation statements missing from the script and the
> database names need changing on the four part names that are not tempdb.
>
Yes, it's just a snippet of code from one of my production systems. I
made the assumption that the OP would be able to adapt it to his needs,
I assumed wrong.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This may
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:

> John Bell wrote:
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||

Quote:

Originally Posted by John Bell

Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This may
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:

> John Bell wrote:
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Interesting code, Tracy, but I am curious - what would become of a document or transaction that a user left open? I would guess that any unsaved changes would be lost, but the document itself is still retained. is that correct?
I do a fair amount of work in GP Modifier & VBA, plus SQL for my company. This is something we've considered doing in the past, to reduce the number of occupied licenses for clients.
Thanks! BTW, you have a neat site...I've bookmarked it.

A User connections question

Is there anyway or SQL script to configure the SQL server to automatically
log users out if their connection remains idle for a predetermined period of
time?
The convenience of allowing users to login to Great Plains in the morning
and staying there all day, even if they actually use the application for onl
y
a fraction of the time, is too expensive to cater to.
I want to start by demanding that users logout of Great Plains when they
will not be using the application for a period of time (i.e. during lunch,
during coffee breaks, during meetings, while they are performing tasks not
requiring immediate access to Great Plains, etc.).
Thank You...Hi
This is one of the things connection pooling would help with, but then you
will not be re-writting the package! I guess you could have a job that check
s
the last_batch time in sysprocesses.
John
"clairvoyant316" wrote:

> Is there anyway or SQL script to configure the SQL server to automatical
ly
> log users out if their connection remains idle for a predetermined period
of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for o
nly
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...|||clairvoyant316 wrote:
> Is there anyway or SQL script to configure the SQL server to automatical
ly
> log users out if their connection remains idle for a predetermined period
of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for o
nly
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
Tried to post this yesterday, apparently it didn't "go". Here's a
script (and table) that I've used to handle this very problem in GP.
CREATE TABLE [dbo].[sb_IdleUsersRemoved] (
[iRowID] [int] IDENTITY (1, 1) NOT NULL ,
[iSPID] [int] NULL ,
[vchUserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[dtDateRemoved] [datetime] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE sb_RemoveIdleUsers
AS
DECLARE @.iSPID INT
DECLARE @.vchCommand VARCHAR(255)
/* Create list of SPIDs that have been idle for 12 hours, and are still
listed in dex_session */
SELECT a.spid, a.loginame
INTO #tempSPIDs
FROM master.dbo.sysprocesses a
INNER JOIN tempdb.dbo.dex_session b
ON a.spid = b.sqlsvr_spid
WHERE GETDATE() - a.last_batch > .5
UNION
SELECT spid, loginame
FROM master.dbo.sysprocesses
WHERE dbid IN (7, 16, 17) AND GETDATE() - last_batch > .5
BEGIN TRANSACTION
/* Record the list of spids and logins that are being removed */
INSERT INTO sb_custom.dbo.sb_IdleUsersRemoved
SELECT spid, loginame, GETDATE()
FROM #tempSPIDs
/* Delete records from dex_lock for any idle sessions */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id FROM tempdb.dbo.dex_session WHERE
sqlsvr_spid IN (SELECT spid FROM #tempSPIDs))
/* Delete records from dex_session for any idle sessions */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid IN (SELECT spid FROM #tempSPIDs)
COMMIT TRANSACTION
/* Kill SQL processes for idle sessions */
SELECT @.iSPID = 0
WHILE EXISTS(SELECT * FROM #tempSPIDs WHERE spid > @.iSPID)
BEGIN
SELECT TOP 1 @.iSPID = spid
FROM #tempSPIDs
WHERE spid > @.iSPID
ORDER BY spid
SELECT @.vchCommand = 'KILL ' + LTRIM(RTRIM(CONVERT(VARCHAR(10),
@.iSPID)))
EXEC (@.vchCommand)
END
DROP TABLE #tempSPIDs
/* Delete records from dex_lock for any records in dex_session that have
no active SQL connection */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
)
/* Delete records from dex_session that have no active SQL connection */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
GO
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for your prompt response.
I ran that script and crashed the MS SQL 2000 with timeout as well as
corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
It didn't kick users out of the Great Plains at all.
Is there any condition or special configuration that I am not aware of to
run that script?
Thank You
"Tracy McKibben" wrote:

> Tried to post this yesterday, apparently it didn't "go". Here's a
> script (and table) that I've used to handle this very problem in GP.|||clairvoyant316 wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
Not likely - the script I sent you simply creates a table and a stored
procedure.

> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
>
The script that I gave you creates a stored procedure. YOU must run
that stored procedure before it will remove any users. Surely you
realized that when you reviewed the script? Sounds like you might be
better off just leaving things alone.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi
tempdb is recreated when you restart SQL Server. If it was really corrupted
I would suspect a hardware failure rather than this script. There seems to b
e
a couple of table creation statements missing from the script and the
database names need changing on the four part names that are not tempdb.
John
"clairvoyant316" wrote:

> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
> Thank You
> "Tracy McKibben" wrote:
>
>|||John Bell wrote:
> Hi
> tempdb is recreated when you restart SQL Server. If it was really corrupte
d
> I would suspect a hardware failure rather than this script. There seems to
be
> a couple of table creation statements missing from the script and the
> database names need changing on the four part names that are not tempdb.
>
Yes, it's just a snippet of code from one of my production systems. I
made the assumption that the OP would be able to adapt it to his needs,
I assumed wrong.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This ma
y
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:

> John Bell wrote:
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

A User connections question

Is there anyway or SQL script to configure the SQL server to automatically
log users out if their connection remains idle for a predetermined period of
time?
The convenience of allowing users to login to Great Plains in the morning
and staying there all day, even if they actually use the application for only
a fraction of the time, is too expensive to cater to.
I want to start by demanding that users logout of Great Plains when they
will not be using the application for a period of time (i.e. during lunch,
during coffee breaks, during meetings, while they are performing tasks not
requiring immediate access to Great Plains, etc.).
Thank You...clairvoyant316 wrote:
> Is there anyway or SQL script to configure the SQL server to automatically
> log users out if their connection remains idle for a predetermined period of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for only
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
Tried to post this yesterday, apparently it didn't "go". Here's a
script (and table) that I've used to handle this very problem in GP.
CREATE TABLE [dbo].[sb_IdleUsersRemoved] (
[iRowID] [int] IDENTITY (1, 1) NOT NULL ,
[iSPID] [int] NULL ,
[vchUserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[dtDateRemoved] [datetime] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE sb_RemoveIdleUsers
AS
DECLARE @.iSPID INT
DECLARE @.vchCommand VARCHAR(255)
/* Create list of SPIDs that have been idle for 12 hours, and are still
listed in dex_session */
SELECT a.spid, a.loginame
INTO #tempSPIDs
FROM master.dbo.sysprocesses a
INNER JOIN tempdb.dbo.dex_session b
ON a.spid = b.sqlsvr_spid
WHERE GETDATE() - a.last_batch > .5
UNION
SELECT spid, loginame
FROM master.dbo.sysprocesses
WHERE dbid IN (7, 16, 17) AND GETDATE() - last_batch > .5
BEGIN TRANSACTION
/* Record the list of spids and logins that are being removed */
INSERT INTO sb_custom.dbo.sb_IdleUsersRemoved
SELECT spid, loginame, GETDATE()
FROM #tempSPIDs
/* Delete records from dex_lock for any idle sessions */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id FROM tempdb.dbo.dex_session WHERE
sqlsvr_spid IN (SELECT spid FROM #tempSPIDs))
/* Delete records from dex_session for any idle sessions */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid IN (SELECT spid FROM #tempSPIDs)
COMMIT TRANSACTION
/* Kill SQL processes for idle sessions */
SELECT @.iSPID = 0
WHILE EXISTS(SELECT * FROM #tempSPIDs WHERE spid > @.iSPID)
BEGIN
SELECT TOP 1 @.iSPID = spid
FROM #tempSPIDs
WHERE spid > @.iSPID
ORDER BY spid
SELECT @.vchCommand = 'KILL ' + LTRIM(RTRIM(CONVERT(VARCHAR(10),
@.iSPID)))
EXEC (@.vchCommand)
END
DROP TABLE #tempSPIDs
/* Delete records from dex_lock for any records in dex_session that have
no active SQL connection */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
)
/* Delete records from dex_session that have no active SQL connection */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
GO
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||clairvoyant316 wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
Not likely - the script I sent you simply creates a table and a stored
procedure.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
>
The script that I gave you creates a stored procedure. YOU must run
that stored procedure before it will remove any users. Surely you
realized that when you reviewed the script? Sounds like you might be
better off just leaving things alone.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi
tempdb is recreated when you restart SQL Server. If it was really corrupted
I would suspect a hardware failure rather than this script. There seems to be
a couple of table creation statements missing from the script and the
database names need changing on the four part names that are not tempdb.
John
"clairvoyant316" wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
> Thank You
> "Tracy McKibben" wrote:
> > Tried to post this yesterday, apparently it didn't "go". Here's a
> > script (and table) that I've used to handle this very problem in GP.
>|||John Bell wrote:
> Hi
> tempdb is recreated when you restart SQL Server. If it was really corrupted
> I would suspect a hardware failure rather than this script. There seems to be
> a couple of table creation statements missing from the script and the
> database names need changing on the four part names that are not tempdb.
>
Yes, it's just a snippet of code from one of my production systems. I
made the assumption that the OP would be able to adapt it to his needs,
I assumed wrong.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This may
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:
> John Bell wrote:
> > Hi
> >
> > tempdb is recreated when you restart SQL Server. If it was really corrupted
> > I would suspect a hardware failure rather than this script. There seems to be
> > a couple of table creation statements missing from the script and the
> > database names need changing on the four part names that are not tempdb.
> >
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

a user can't be deleted...

Hi,
I've just installed SQL Server 2005, and mistakenly add a user in
ServerName\Database\master; I added two kind of schema into that user.
But when I tried to delete the user, SQL Server 2005 tells me that database
user have schema assigned to it thus can't be remove.
Is there a way to do delete it?
PS: I have another serious problem regarding SQL Server 2005 database,
please take a look at it in "SQL Server Server newsgroup" under "failed to
connect to SQL Server's database!" thread. Any suggestions and help is
greatly appreciated.
Thank you.You have to first remove the schemas assigned to the user BEFORE you can
remove the user.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dhow" <Dhow@.discussions.microsoft.com> wrote in message
news:958F2A86-5F41-41B6-92E8-9AB888FCD441@.microsoft.com...
> Hi,
> I've just installed SQL Server 2005, and mistakenly add a user in
> ServerName\Database\master; I added two kind of schema into that user.
> But when I tried to delete the user, SQL Server 2005 tells me that
> database
> user have schema assigned to it thus can't be remove.
> Is there a way to do delete it?
> PS: I have another serious problem regarding SQL Server 2005 database,
> please take a look at it in "SQL Server Server newsgroup" under "failed to
> connect to SQL Server's database!" thread. Any suggestions and help is
> greatly appreciated.
> Thank you.

A URL expression problem with a Jump to command

I have a report that uses the table control, the last column has id's for
dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
any of the other Columns in a row I would like to be able to grab the id from
the last column in the row and insert it into the expression for the Jump to
url feature.
this url works fin
="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={535d58cc-a1b3-da11-9864-001320020c86}','_blank'))"
this one gives me an Error on pag
="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={'&Fields!res_dealCompanyid.Value&'}','_blank'))"
How can I find out what is wrong
MikeI got this to work, my problem was that the dealCompanyid value was not a
string so I had to do a Fields!res_dealcompanyid.Value.tostring() then it
worked fine.
Parameters!CRMServer.Value just holds a Server info
="javascript:void(window.open('http://" & Parameters!CRMServer.Value &
"/EE2/DealCompany.aspx?id=" & Fields!res_dealcompanyid.Value.tostring() & "',
'_blank'))"
"Hotwheels" wrote:
> I have a report that uses the table control, the last column has id's for
> dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
> any of the other Columns in a row I would like to be able to grab the id from
> the last column in the row and insert it into the expression for the Jump to
> url feature.
> this url works fine
> ="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={535d58cc-a1b3-da11-9864-001320020c86}','_blank'))"
> this one gives me an Error on page
> ="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={'&Fields!res_dealCompanyid.Value&'}','_blank'))"
> How can I find out what is wrong
> Mike
>

A truncation occurred during evaluation of the expression

First of all, I get the following error message for one of my packages which uses user variables:

SSIS package "UsageAnalysis.dtsx" starting.
Information: 0x4004300A at Perform xmlState Shredding, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001700E at UsageAnalysis: A truncation occurred during evaluation of the expression.
Error: 0xC0019004 at UsageAnalysis: The expression for variable "GetAnalysisData" failed evaluation. There was an error in the expression.
Error: 0xC02020E9 at Update Analysis Table, UsageAnalysis Source [1]: Accessing variable "User::GetAnalysisData" failed with error code 0xC001700E.
Error: 0xC0024107 at Update Analysis Table: There were errors during task validation.
Warning: 0x80019002 at Usage Analysis Process: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "UsageAnalysis.dtsx" finished: Failure.

Now my package has the following variables:

GetMaxUsageID: scope package level, type string, statement SELECT MAX(UsageID) AS MaxUsageID FROM XX.XXX

MaxUsageID: scope package level, type int32, default value 0, value get assigned from the following statement executed from sql task that runs GetMaxUsageID variable as above

GetAnalysisData: scope package level, type string, Evaluate as Expression

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 8, 1252) @.[User::MaxUsageID]

The package has worked fine until MaxUsageID value reached to 10,00,000 and since then I have been getting above mentioned error message. The problematic step is related to Data Flow task where I use GetAnalysisData. I have tried replacing user variable with literal as follows

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000"

the error message stays the same. Please note that package has worked fine before and it still works ok if I don't use user variables. Obviously, some of you would see eliminating user variables as workaround but I would appreciate if cause of that error message could be investigated.

Thanks,

Asaf

Are you sure MAX(UsageID) is 1,000,000 and not 100,000,000?

Try making your cast a longer length in the GetAnalysisData variable. Perhaps: (DT_STR,20,1252)@.[User::MaxUsageID]

|||

Hi Phil,

Thanks for your reply but I am afraid I already have tried the solution you suggested. Max(UsageID) is calculated against sql table and then stored to MaxUsageID user variable.

select max(usageID) from dbo.XXX

--1027005

So you could see value by no means is tens of millions as you anticipated. I also have tried changing DT_STR string length parameter to larger values as proposed

SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 20, 1252) @.[User::MaxUsageID]

Even when I have no casting involved such as

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000"

I still get the same truncation error message. I would appreciate if you could suggest something else which obviously I am missing here.

Thanks,

Asaf

|||

Phil,

I have worked it out.

It looks like one of the SSIS bugs. Even though I updated the expression by changing (DT_STR, 6, 1252) to (DT_STR, 12, 1252) so that I could avoid any obvious truncation issues for values above 1 million and after saving the package I rebuilt it but project would fail whenever I executed it.

When I opened the project again, I ended up having the old value for (DT_STR, 6, 1252) in xml code view. Somehow SSIS wouldn't save the updated expression. Hence I kept getting that error message even though I had reasons to believe I had done everything right.

To resolve that, I just decided to define a new variable with required DT_STR string length and my package works without any complain.

Thanks,

Asaf

|||

Sounds like you may have been trying to update the "Value" parameter, not the "Expression" parameter. In that case, it would revert back to whatever you had in the expression.

With that being said, if it is a bug and you can reproduce it in the future, please submit it at http://connect.microsoft.com/sqlserver/feedback and report back here with the link to your submission. But please only submit it if you can reproduce it.

Thanks,

Phil

sql

A truncation occurred during evaluation of the expression

First of all, I get the following error message for one of my packages which uses user variables:

SSIS package "UsageAnalysis.dtsx" starting.
Information: 0x4004300A at Perform xmlState Shredding, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001700E at UsageAnalysis: A truncation occurred during evaluation of the expression.
Error: 0xC0019004 at UsageAnalysis: The expression for variable "GetAnalysisData" failed evaluation. There was an error in the expression.
Error: 0xC02020E9 at Update Analysis Table, UsageAnalysis Source [1]: Accessing variable "User::GetAnalysisData" failed with error code 0xC001700E.
Error: 0xC0024107 at Update Analysis Table: There were errors during task validation.
Warning: 0x80019002 at Usage Analysis Process: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "UsageAnalysis.dtsx" finished: Failure.

Now my package has the following variables:

GetMaxUsageID: scope package level, type string, statement SELECT MAX(UsageID) AS MaxUsageID FROM XX.XXX

MaxUsageID: scope package level, type int32, default value 0, value get assigned from the following statement executed from sql task that runs GetMaxUsageID variable as above

GetAnalysisData: scope package level, type string, Evaluate as Expression

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 8, 1252) @.[User::MaxUsageID]

The package has worked fine until MaxUsageID value reached to 10,00,000 and since then I have been getting above mentioned error message. The problematic step is related to Data Flow task where I use GetAnalysisData. I have tried replacing user variable with literal as follows

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000"

the error message stays the same. Please note that package has worked fine before and it still works ok if I don't use user variables. Obviously, some of you would see eliminating user variables as workaround but I would appreciate if cause of that error message could be investigated.

Thanks,

Asaf

Are you sure MAX(UsageID) is 1,000,000 and not 100,000,000?

Try making your cast a longer length in the GetAnalysisData variable. Perhaps: (DT_STR,20,1252)@.[User::MaxUsageID]

|||

Hi Phil,

Thanks for your reply but I am afraid I already have tried the solution you suggested. Max(UsageID) is calculated against sql table and then stored to MaxUsageID user variable.

select max(usageID) from dbo.XXX

--1027005

So you could see value by no means is tens of millions as you anticipated. I also have tried changing DT_STR string length parameter to larger values as proposed

SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 20, 1252) @.[User::MaxUsageID]

Even when I have no casting involved such as

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000"

I still get the same truncation error message. I would appreciate if you could suggest something else which obviously I am missing here.

Thanks,

Asaf

|||

Phil,

I have worked it out.

It looks like one of the SSIS bugs. Even though I updated the expression by changing (DT_STR, 6, 1252) to (DT_STR, 12, 1252) so that I could avoid any obvious truncation issues for values above 1 million and after saving the package I rebuilt it but project would fail whenever I executed it.

When I opened the project again, I ended up having the old value for (DT_STR, 6, 1252) in xml code view. Somehow SSIS wouldn't save the updated expression. Hence I kept getting that error message even though I had reasons to believe I had done everything right.

To resolve that, I just decided to define a new variable with required DT_STR string length and my package works without any complain.

Thanks,

Asaf

|||

Sounds like you may have been trying to update the "Value" parameter, not the "Expression" parameter. In that case, it would revert back to whatever you had in the expression.

With that being said, if it is a bug and you can reproduce it in the future, please submit it at http://connect.microsoft.com/sqlserver/feedback and report back here with the link to your submission. But please only submit it if you can reproduce it.

Thanks,

Phil

A transport-level error has occurred when sending the request to the server. (provider: TC

when i enter the user name, Password and click enter in my site

it showing below error..

A transport-level error has occurred when sending the request to the server.

(provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

What may be the problem..

This does not sound like a SQL Server problem, it's more likely something is acting up in the network.

Have a look at 2 links below if it still fail after few retries:
http://www.mcse.ms/message2316445.html
http://channel9.msdn.com/ShowPost.aspx?PostID=243278

|||

i am accessing the site through internet..

if i reload the error page it loading successfully..

sql

A temporary database

I have three aspx pages with one form in every page, these three pages allows to register a user. Now when I submit every form on every page the data is sent to the database, but somebody has recommended me to store data in a temporary database file, and when user submit last page (form) store all the data in main database.

I use Sql 2000 Server Personal Edition and VB.NET for aspx pages.
How can I use this temporary database in my aspx page?

Now my first form-page:

<%@.Import Namespace="System.Data" %>
<%@.Import Namespace="System.Data.SqlClient" %
<script language="VB" runat="server">
Dim strConnection As New SqlConnection(ConfigurationSettings.AppSettings("myConn"))
Sub Send_data(sender As Object, e As EventArgs)
 Dim CmdInsert As New SqlCommand("new_user1", strConnection)
 CmdInsert.CommandType = CommandType.StoredProcedure
 Dim InsertForm As New SqlDataAdapter()
 InsertForm.InsertCommand = CmdInsert
 CmdInsert.Parameters.Add("@.Id", SqlDbType.bigint)
 CmdInsert.Parameters("@.Id").Direction = ParameterDirection.Output
 CmdInsert.Parameters.Add(New SqlParameter("@.e_mail", SqlDbType.varchar, 50, "e_mail"))
 CmdInsert.Parameters("@.e_mail").Value = mail.Text()
……….
 strConnection.open()
 CmdInsert.ExecuteNonQuery
 Dim IdUser As integer = CmdInsert.Parameters("@.Id").Value
 strConnection.close()
End Sub

Thanks(1) you can generate an id for each user
(2) insert all the data from each page into some temp database( which you have already created ) using the id
(3) finally call some stored proc tht will move all the info from the temp db to the main one..again using the id...if the user decides to cancel ( at any stage) just empty the temp table.

hth|||Could you recommend me an article, manual, reference,etc.. to start with temporary databases please? I have never worked with them.|||its just another table thts xactly similar in structure to your actual table that you want to put the records in..there are some sys temp tables but i was referring to user created table...sorry if i misled you..

hth|||Before I take the decision of using 'temp tables' I want to know, sure, if it is better passing all the values of the forms with a session variable through the pages until the end, and then do the insert into the main (real) database, or if it is better to do the inserts in every page and use 'temp tables'. Or do the inserts in every page and store the userName and Password in a session variable and do the insert of these 'key' fields in the last page when the registration is complete.

Some experienced ideas please?|||It depends upon the amount of data. Will have have 100 bytes of data over the screens, or 20000 bytes? If 100 bytes, session will be fine, if 20000 then probably not. In addition, rather than using a temp table, you might just want to add a "pending" flag in the real table, and the add data as you go along (with pending set to true) and then on the last page, save last of the data and clear the pending flag.|||I don' t know exactly the amount of data, it is more or less ten fields per page (3 pages). Five with number values and five with text values with max length 50, and some text area field.
Anyway, regardless of the amount of data, Is it better to use a 'pending' flag in the real table? Now I don' t know anything about this system.

Thanks,
Cesar|||I tend to prefer it, as it saves some rewriting of data. Are you already using the Session for other things? If so, it might be reasonable to add this information and then not persist anything in the DB until they are done. With either other plan, you need to have a way to clean up junk (Pending in live or records in the temp).|||Every character is one byte, isn' t it? In afirmative case the amount of data per page will be less than 200 bytes.|||Ok thank you very much|||.NET uses Unicode, so each character in a string is 2 bytes. Either 200 or 400 bytes would be reasonable for the session, unless you have thousands of users, or unless you want to avoid using the session for other reasons.

A small question

Hi,
I want to give execute persmission to a user called "user1" on xp_cmdshell.
How do i do this through an SQL script?
Thanks in advance.
/AQI figured it out myself... Thanks :-)
"MAQ" <dingdongdang@.msn.com> wrote in message
news:euLv$7TKFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to give execute persmission to a user called "user1" on
> xp_cmdshell.
> How do i do this through an SQL script?
> Thanks in advance.
>
> /AQ
>

A Simple Problem. But....

Hi everybody,

I have a requirement in which a user fetches some records from a table. I want that records to be locked for that user so that when other fetches records these should be records not fetched earlier. Currently I am using a stored proc in which I rerun the select statement to lock the records in temp table. Any better solution ?

Thanks in advance

You don't want users to even be able to READ rows that have been selected and locked? Not sure you can do that, as they can SELECT even while the locks are in place. Sounds like you are doing it in a way that makes sense already... that you control which rows are "checked out" and then not allow others to get them untill they are checked in... You could add a status field on your table and then use that to allow an application to select those rows or not... Bruce|||

Locks can be a good thing and they can also be devastating if mismanaged.

You can tweak the isolation level to help most situations.

How long do you plan to hold these locks?

Why do you need to manage them?

What is your objective?

Please provide a little more detail and depth on your situation and needs.

|||How about updating the table, using the OUTPUT clause to return what you've changed, like:

UPDATE thetable
SET islocked = 1
OUTPUT DELETED.*
WHERE islocked = 0
AND ... --other criteria goes here

This way, you'll get the locked rows, and the next user won't be able to. If you want to mark who locked it, then perhaps use a different type for islocked.

Hope this helps.

Rob|||

Hi Dale,

I am making a call center application in two or three agents make outbound calls to customers. If there are 100 calls to be made then each of my agent sees these 100 entries and makes multiple calls to the same customer. I want that when an agent logs in then it should some fixed number of entries(say 25) and when another agent logs in then it should see entries not fetched earlier.

In this way each agent will have different number for outbound call. I am not locking the records per se. In my main table I have a primary key. When I fetch the records I make an entry in the temp table for that records. For other agent I fetch the records that are not in temp table.

|||I would add a column to the table for the [Agent ID], have a Agent first 'mark' n number of rows with his/her [Agent ID], and then retreive the rows that were marked.|||

Ah.

It's an allocation challenge.

Like Arnie says, add the agent id to the call record.

Or

Create an intermediate cross-reference/assignment table that matches call records to agent's.

you can then easily manipulate this table with inserts, updates, deletes.

And when you create a new set of assignments you just get those calls that aren't present in the assignment table.

|||

Hi Arnie,

Thanks for sparing time. This is exactly what I am doing currently but I don't give agent to mark the rows. Some fixed number of rows are marked(in a temp table) for that agent. And these rows are released when the agent logs out.

Thanks

|||Did you try using the OUTPUT clause of an UPDATE statement? Regardless of whether you call use "allocatedto = system_user" or "islocked = 1", the effect will be similar.

(Following the order of posts here is awkward - your later post appeared higher than mine in the thread tree)

Rob

A Simple Problem. But....

Hi everybody,

I have a requirement in which a user fetches some records from a table. I want that records to be locked for that user so that when other fetches records these should be records not fetched earlier. Currently I am using a stored proc in which I rerun the select statement to lock the records in temp table. Any better solution ?

Thanks in advance

You don't want users to even be able to READ rows that have been selected and locked? Not sure you can do that, as they can SELECT even while the locks are in place. Sounds like you are doing it in a way that makes sense already... that you control which rows are "checked out" and then not allow others to get them untill they are checked in... You could add a status field on your table and then use that to allow an application to select those rows or not... Bruce|||

Locks can be a good thing and they can also be devastating if mismanaged.

You can tweak the isolation level to help most situations.

How long do you plan to hold these locks?

Why do you need to manage them?

What is your objective?

Please provide a little more detail and depth on your situation and needs.

|||How about updating the table, using the OUTPUT clause to return what you've changed, like:

UPDATE thetable
SET islocked = 1
OUTPUT DELETED.*
WHERE islocked = 0
AND ... --other criteria goes here

This way, you'll get the locked rows, and the next user won't be able to. If you want to mark who locked it, then perhaps use a different type for islocked.

Hope this helps.

Rob|||

Hi Dale,

I am making a call center application in two or three agents make outbound calls to customers. If there are 100 calls to be made then each of my agent sees these 100 entries and makes multiple calls to the same customer. I want that when an agent logs in then it should some fixed number of entries(say 25) and when another agent logs in then it should see entries not fetched earlier.

In this way each agent will have different number for outbound call. I am not locking the records per se. In my main table I have a primary key. When I fetch the records I make an entry in the temp table for that records. For other agent I fetch the records that are not in temp table.

|||I would add a column to the table for the [Agent ID], have a Agent first 'mark' n number of rows with his/her [Agent ID], and then retreive the rows that were marked.|||

Ah.

It's an allocation challenge.

Like Arnie says, add the agent id to the call record.

Or

Create an intermediate cross-reference/assignment table that matches call records to agent's.

you can then easily manipulate this table with inserts, updates, deletes.

And when you create a new set of assignments you just get those calls that aren't present in the assignment table.

|||

Hi Arnie,

Thanks for sparing time. This is exactly what I am doing currently but I don't give agent to mark the rows. Some fixed number of rows are marked(in a temp table) for that agent. And these rows are released when the agent logs out.

Thanks

|||Did you try using the OUTPUT clause of an UPDATE statement? Regardless of whether you call use "allocatedto = system_user" or "islocked = 1", the effect will be similar.

(Following the order of posts here is awkward - your later post appeared higher than mine in the thread tree)

Rob

A simple INSERT Problem

Dev Tool: VB6
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(??)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
Rick
Try stepping through the debugger and check to see if the connection is open.
"Rick" wrote:

> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT staments.
> Having the same connection and user id. When I execute the stored procedure
> in the Query Analyzer console I have no problem, but when I execute it in a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(??)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick
|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
Rick
|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" from your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from the INSERT, but I don't
recommend that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick

A simple INSERT Problem

Dev Tool: VB6
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments
.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(??)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
RickTry stepping through the debugger and check to see if the connection is open
.
"Rick" wrote:

> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT stamen
ts.
> Having the same connection and user id. When I execute the stored procedur
e
> in the Query Analyzer console I have no problem, but when I execute it in
a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(??
)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
--
Rick|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" f
rom your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from t
he INSERT, but I don't
recommend that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick

A simple INSERT Problem

Dev Tool: VB6
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(¡?)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
--
RickTry stepping through the debugger and check to see if the connection is open.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT staments.
> Having the same connection and user id. When I execute the stored procedure
> in the Query Analyzer console I have no problem, but when I execute it in a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(¡?)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
--
Rick|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" from your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from the INSERT, but I don't
recommend that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick