Hi,
I have created a VB program to perform DTS tasks for data transfer from
ACCESS database to SQL Server. The DTS are created by saving the actual DTS
packages as VB file and used those bas files in VP app to run the DTS
programmatically.
The process works fine most of the time.
But occassionally the DTS process is getting locked. The two connections
from the DTS package to SQL server database gains DB lock on the database
and so the process does not go further .
Can any body know why this is happenning?
what could be the solution for this problem?
please help.
thanks
SamTracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Sam" wrote:
> Hi,
> I have created a VB program to perform DTS tasks for data transfer from
> ACCESS database to SQL Server. The DTS are created by saving the actual DT
S
> packages as VB file and used those bas files in VP app to run the DTS
> programmatically.
> The process works fine most of the time.
> But occassionally the DTS process is getting locked. The two connections
> from the DTS package to SQL server database gains DB lock on the database
> and so the process does not go further .
> Can any body know why this is happenning?
> what could be the solution for this problem?
> please help.
> thanks
> Sam
>
>
Showing posts with label created. Show all posts
Showing posts with label created. Show all posts
Tuesday, March 27, 2012
Wednesday, March 21, 2012
deadlock on tempdb..sysindexes
Greetings,
I have stored procedure dumping resultset into created temporary table.
Every 15-30 minutes we have deadlock and always on sysindexes in tempdb.
Problem is I that can not change stored procedure and not sure how to stop
locking sysindexes since stored proc will take between 5-15 seconds to run
depending on date range supplied.
I would appreciate any suggestions how to resolve this issue
PS: Code goes like this
create table #temp(...)
insert into #temp
execute sp_StoredProc
--
SaxonThe problem appears to be that the stored procedure is executing within a
transaction. This is unavoidable, since the INSERT...EXEC statement starts
a transaction prior to executing the procedure. To avoid the deadlocks, you
MUST alter the stored procedure. If you can't alter it, then make a copy
and alter that. Change the procedure so that it executes an INSERT
statement into the temp table. (If a temp table is created before executing
the stored procedure, it is available within the body of the stored
procedure.) This will eliminate the transaction
You should avoid creating, altering or deleting temporary objects within a
transaction. This includes both tables, indexes and constraints. You
should avoid executing procedures within a transaction. For this reason, I
generally avoid INSERT...EXEC.
"Saxon" <Saxon@.discussions.microsoft.com> wrote in message
news:0E06645B-1552-4B8A-BC79-5B888D9D9D7B@.microsoft.com...
> Greetings,
> I have stored procedure dumping resultset into created temporary table.
> Every 15-30 minutes we have deadlock and always on sysindexes in tempdb.
> Problem is I that can not change stored procedure and not sure how to stop
> locking sysindexes since stored proc will take between 5-15 seconds to run
> depending on date range supplied.
> I would appreciate any suggestions how to resolve this issue
> PS: Code goes like this
> create table #temp(...)
> insert into #temp
> execute sp_StoredProc
> --
> Saxon|||Thanks Brian,
so basically if I create temp table and call stored proc to insert into
table instead of using INSERT... EXEC it would not cause deadlock since no
transactions would be started.
PS: Why inserting into temp table would hold lock on sysindexes anyway? I
tried to find some info on that but no luck.
Regards
Saxon
"Brian Selzer" wrote:
> The problem appears to be that the stored procedure is executing within a
> transaction. This is unavoidable, since the INSERT...EXEC statement start
s
> a transaction prior to executing the procedure. To avoid the deadlocks, y
ou
> MUST alter the stored procedure. If you can't alter it, then make a copy
> and alter that. Change the procedure so that it executes an INSERT
> statement into the temp table. (If a temp table is created before executi
ng
> the stored procedure, it is available within the body of the stored
> procedure.) This will eliminate the transaction
> You should avoid creating, altering or deleting temporary objects within a
> transaction. This includes both tables, indexes and constraints. You
> should avoid executing procedures within a transaction. For this reason,
I
> generally avoid INSERT...EXEC.
>
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:0E06645B-1552-4B8A-BC79-5B888D9D9D7B@.microsoft.com...
>
>|||The lock isn't caused by inserting, it's caused by creating, altering, or
deleting a temporary object within the procedure! The problem is that
normally, when a procedure runs, any transactions must be explicitly started
within the body of the proc. INSERT...EXEC wraps the procedure call in a
transaction. There are several articles on MSDN about lock contention and
blocking--some cite concurrency issues with tempdb. (There are fixes for
that in SP4.)
"Saxon" <Saxon@.discussions.microsoft.com> wrote in message
news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
> Thanks Brian,
> so basically if I create temp table and call stored proc to insert into
> table instead of using INSERT... EXEC it would not cause deadlock since no
> transactions would be started.
> PS: Why inserting into temp table would hold lock on sysindexes anyway? I
> tried to find some info on that but no luck.
> Regards
> --
> Saxon
>
> "Brian Selzer" wrote:
>|||Thank you kindly Brian.
Much appreciated.
Regards
Saxon
"Brian Selzer" wrote:
> The lock isn't caused by inserting, it's caused by creating, altering, or
> deleting a temporary object within the procedure! The problem is that
> normally, when a procedure runs, any transactions must be explicitly start
ed
> within the body of the proc. INSERT...EXEC wraps the procedure call in a
> transaction. There are several articles on MSDN about lock contention and
> blocking--some cite concurrency issues with tempdb. (There are fixes for
> that in SP4.)
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
>
>|||Thanks for this useful description of the problem.
If we creates at temporary table in the procedure and fill data into it with
a function, will that cause a transaction too?
create table #temp(...)
insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
"Brian Selzer" wrote:
> The lock isn't caused by inserting, it's caused by creating, altering, or
> deleting a temporary object within the procedure! The problem is that
> normally, when a procedure runs, any transactions must be explicitly start
ed
> within the body of the proc. INSERT...EXEC wraps the procedure call in a
> transaction. There are several articles on MSDN about lock contention and
> blocking--some cite concurrency issues with tempdb. (There are fixes for
> that in SP4.)
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
>
>|||On Wed, 23 Nov 2005 03:36:11 -0800, winther wrote:
>Thanks for this useful description of the problem.
>If we creates at temporary table in the procedure and fill data into it wit
h
>a function, will that cause a transaction too?
>create table #temp(...)
>insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
Hi winther,
Yes. Every modification is automatically part of a transaction. If you
didn't start one explicitly, it will be started implicitly.
If SET IMPLICIT_TRANSACTION is OFF, the implicitly started transaction
will also be implicitly committed after each statement. With this
setting to ON, the server waits for an explicit COMMIT or ROLLBACK to
end the transaction.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There will be a transaction, but it won't put a lock on sysindexes because
the create table occurs apart from the transaction started for the insert.
"winther" <winther@.discussions.microsoft.com> wrote in message
news:0A8FC1D4-1571-45B8-91A8-29656995C285@.microsoft.com...
> Thanks for this useful description of the problem.
> If we creates at temporary table in the procedure and fill data into it
> with
> a function, will that cause a transaction too?
> create table #temp(...)
> insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
>
> "Brian Selzer" wrote:
>
I have stored procedure dumping resultset into created temporary table.
Every 15-30 minutes we have deadlock and always on sysindexes in tempdb.
Problem is I that can not change stored procedure and not sure how to stop
locking sysindexes since stored proc will take between 5-15 seconds to run
depending on date range supplied.
I would appreciate any suggestions how to resolve this issue
PS: Code goes like this
create table #temp(...)
insert into #temp
execute sp_StoredProc
--
SaxonThe problem appears to be that the stored procedure is executing within a
transaction. This is unavoidable, since the INSERT...EXEC statement starts
a transaction prior to executing the procedure. To avoid the deadlocks, you
MUST alter the stored procedure. If you can't alter it, then make a copy
and alter that. Change the procedure so that it executes an INSERT
statement into the temp table. (If a temp table is created before executing
the stored procedure, it is available within the body of the stored
procedure.) This will eliminate the transaction
You should avoid creating, altering or deleting temporary objects within a
transaction. This includes both tables, indexes and constraints. You
should avoid executing procedures within a transaction. For this reason, I
generally avoid INSERT...EXEC.
"Saxon" <Saxon@.discussions.microsoft.com> wrote in message
news:0E06645B-1552-4B8A-BC79-5B888D9D9D7B@.microsoft.com...
> Greetings,
> I have stored procedure dumping resultset into created temporary table.
> Every 15-30 minutes we have deadlock and always on sysindexes in tempdb.
> Problem is I that can not change stored procedure and not sure how to stop
> locking sysindexes since stored proc will take between 5-15 seconds to run
> depending on date range supplied.
> I would appreciate any suggestions how to resolve this issue
> PS: Code goes like this
> create table #temp(...)
> insert into #temp
> execute sp_StoredProc
> --
> Saxon|||Thanks Brian,
so basically if I create temp table and call stored proc to insert into
table instead of using INSERT... EXEC it would not cause deadlock since no
transactions would be started.
PS: Why inserting into temp table would hold lock on sysindexes anyway? I
tried to find some info on that but no luck.
Regards
Saxon
"Brian Selzer" wrote:
> The problem appears to be that the stored procedure is executing within a
> transaction. This is unavoidable, since the INSERT...EXEC statement start
s
> a transaction prior to executing the procedure. To avoid the deadlocks, y
ou
> MUST alter the stored procedure. If you can't alter it, then make a copy
> and alter that. Change the procedure so that it executes an INSERT
> statement into the temp table. (If a temp table is created before executi
ng
> the stored procedure, it is available within the body of the stored
> procedure.) This will eliminate the transaction
> You should avoid creating, altering or deleting temporary objects within a
> transaction. This includes both tables, indexes and constraints. You
> should avoid executing procedures within a transaction. For this reason,
I
> generally avoid INSERT...EXEC.
>
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:0E06645B-1552-4B8A-BC79-5B888D9D9D7B@.microsoft.com...
>
>|||The lock isn't caused by inserting, it's caused by creating, altering, or
deleting a temporary object within the procedure! The problem is that
normally, when a procedure runs, any transactions must be explicitly started
within the body of the proc. INSERT...EXEC wraps the procedure call in a
transaction. There are several articles on MSDN about lock contention and
blocking--some cite concurrency issues with tempdb. (There are fixes for
that in SP4.)
"Saxon" <Saxon@.discussions.microsoft.com> wrote in message
news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
> Thanks Brian,
> so basically if I create temp table and call stored proc to insert into
> table instead of using INSERT... EXEC it would not cause deadlock since no
> transactions would be started.
> PS: Why inserting into temp table would hold lock on sysindexes anyway? I
> tried to find some info on that but no luck.
> Regards
> --
> Saxon
>
> "Brian Selzer" wrote:
>|||Thank you kindly Brian.
Much appreciated.
Regards
Saxon
"Brian Selzer" wrote:
> The lock isn't caused by inserting, it's caused by creating, altering, or
> deleting a temporary object within the procedure! The problem is that
> normally, when a procedure runs, any transactions must be explicitly start
ed
> within the body of the proc. INSERT...EXEC wraps the procedure call in a
> transaction. There are several articles on MSDN about lock contention and
> blocking--some cite concurrency issues with tempdb. (There are fixes for
> that in SP4.)
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
>
>|||Thanks for this useful description of the problem.
If we creates at temporary table in the procedure and fill data into it with
a function, will that cause a transaction too?
create table #temp(...)
insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
"Brian Selzer" wrote:
> The lock isn't caused by inserting, it's caused by creating, altering, or
> deleting a temporary object within the procedure! The problem is that
> normally, when a procedure runs, any transactions must be explicitly start
ed
> within the body of the proc. INSERT...EXEC wraps the procedure call in a
> transaction. There are several articles on MSDN about lock contention and
> blocking--some cite concurrency issues with tempdb. (There are fixes for
> that in SP4.)
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
>
>|||On Wed, 23 Nov 2005 03:36:11 -0800, winther wrote:
>Thanks for this useful description of the problem.
>If we creates at temporary table in the procedure and fill data into it wit
h
>a function, will that cause a transaction too?
>create table #temp(...)
>insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
Hi winther,
Yes. Every modification is automatically part of a transaction. If you
didn't start one explicitly, it will be started implicitly.
If SET IMPLICIT_TRANSACTION is OFF, the implicitly started transaction
will also be implicitly committed after each statement. With this
setting to ON, the server waits for an explicit COMMIT or ROLLBACK to
end the transaction.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There will be a transaction, but it won't put a lock on sysindexes because
the create table occurs apart from the transaction started for the insert.
"winther" <winther@.discussions.microsoft.com> wrote in message
news:0A8FC1D4-1571-45B8-91A8-29656995C285@.microsoft.com...
> Thanks for this useful description of the problem.
> If we creates at temporary table in the procedure and fill data into it
> with
> a function, will that cause a transaction too?
> create table #temp(...)
> insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
>
> "Brian Selzer" wrote:
>
Deadlock Issue when dropping/creating tables
We are getting deadlock errors (sporadically) on a batch job we've created.
if you ran your stored proc and then run it again, you'll have problem since it's still being used by the first one. possible locks will happen. try to create a tempoary table with randomly-generated table names...
This job runs against a SQL Server 2000 back-end.
The first step of the batch job is to run a DDL script to drop and create 4 tables that are used in the job. The tables are only used during this job and are not accessed by any other process or application.
The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.
The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:
Msg 1205, Level 13, State 54, Server SQL\APP_PROD, Line 7
Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.
Any thoughts or help would be greatly appreciated.
Quote:
Originally Posted by DWiggin
We are getting deadlock errors (sporadically) on a batch job we've created.
This job runs against a SQL Server 2000 back-end.
The first step of the batch job is to run a DDL script to drop and create 4 tables that are used in the job. The tables are only used during this job and are not accessed by any other process or application.
The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.
The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:
Msg 1205, Level 13, State 54, Server SQL\APP_PROD, Line 7
Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.
Any thoughts or help would be greatly appreciated.
if you ran your stored proc and then run it again, you'll have problem since it's still being used by the first one. possible locks will happen. try to create a tempoary table with randomly-generated table names...
Sunday, March 11, 2012
deadlock
Hi,
I have created a VB program to perform DTS tasks for data transfer from
ACCESS database to SQL Server. The DTS are created by saving the actual DTS
packages as VB file and used those bas files in VP app to run the DTS
programmatically.
The process works fine most of the time.
But occassionally the DTS process is getting locked. The two connections
from the DTS package to SQL server database gains DB lock on the database
and so the process does not go further .
Can any body know why this is happenning?
what could be the solution for this problem?
please help.
thanks
Sam
How are you sure it's a deadlock instead of a normal blocking?
You can set up trace flag -T1204 and -T3605 in the start up parameters on
the SQL server and capture deadlock details in the SQL error log. Or if you
have lumigent Log Explorer, you can set up alert on deadlocks. YOu can also
use SQL profiler to capture a trace using Locks:Deadlocks and Lock:Deadlock
Chain.
Richard
"Sam" <samirsoni@.hotmail.com> wrote in message
news:elDzAt6MFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have created a VB program to perform DTS tasks for data transfer from
> ACCESS database to SQL Server. The DTS are created by saving the actual
> DTS
> packages as VB file and used those bas files in VP app to run the DTS
> programmatically.
> The process works fine most of the time.
> But occassionally the DTS process is getting locked. The two connections
> from the DTS package to SQL server database gains DB lock on the database
> and so the process does not go further .
> Can any body know why this is happenning?
> what could be the solution for this problem?
> please help.
> thanks
> Sam
>
>
I have created a VB program to perform DTS tasks for data transfer from
ACCESS database to SQL Server. The DTS are created by saving the actual DTS
packages as VB file and used those bas files in VP app to run the DTS
programmatically.
The process works fine most of the time.
But occassionally the DTS process is getting locked. The two connections
from the DTS package to SQL server database gains DB lock on the database
and so the process does not go further .
Can any body know why this is happenning?
what could be the solution for this problem?
please help.
thanks
Sam
How are you sure it's a deadlock instead of a normal blocking?
You can set up trace flag -T1204 and -T3605 in the start up parameters on
the SQL server and capture deadlock details in the SQL error log. Or if you
have lumigent Log Explorer, you can set up alert on deadlocks. YOu can also
use SQL profiler to capture a trace using Locks:Deadlocks and Lock:Deadlock
Chain.
Richard
"Sam" <samirsoni@.hotmail.com> wrote in message
news:elDzAt6MFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have created a VB program to perform DTS tasks for data transfer from
> ACCESS database to SQL Server. The DTS are created by saving the actual
> DTS
> packages as VB file and used those bas files in VP app to run the DTS
> programmatically.
> The process works fine most of the time.
> But occassionally the DTS process is getting locked. The two connections
> from the DTS package to SQL server database gains DB lock on the database
> and so the process does not go further .
> Can any body know why this is happenning?
> what could be the solution for this problem?
> please help.
> thanks
> Sam
>
>
Thursday, March 8, 2012
deadlock
Hi,
I have created a VB program to perform DTS tasks for data transfer from
ACCESS database to SQL Server. The DTS are created by saving the actual DTS
packages as VB file and used those bas files in VP app to run the DTS
programmatically.
The process works fine most of the time.
But occassionally the DTS process is getting locked. The two connections
from the DTS package to SQL server database gains DB lock on the database
and so the process does not go further .
Can any body know why this is happenning?
what could be the solution for this problem?
please help.
thanks
SamHow are you sure it's a deadlock instead of a normal blocking?
You can set up trace flag -T1204 and -T3605 in the start up parameters on
the SQL server and capture deadlock details in the SQL error log. Or if you
have lumigent Log Explorer, you can set up alert on deadlocks. YOu can also
use SQL profiler to capture a trace using Locks:Deadlocks and Lock:Deadlock
Chain.
Richard
"Sam" <samirsoni@.hotmail.com> wrote in message
news:elDzAt6MFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have created a VB program to perform DTS tasks for data transfer from
> ACCESS database to SQL Server. The DTS are created by saving the actual
> DTS
> packages as VB file and used those bas files in VP app to run the DTS
> programmatically.
> The process works fine most of the time.
> But occassionally the DTS process is getting locked. The two connections
> from the DTS package to SQL server database gains DB lock on the database
> and so the process does not go further .
> Can any body know why this is happenning?
> what could be the solution for this problem?
> please help.
> thanks
> Sam
>
>
I have created a VB program to perform DTS tasks for data transfer from
ACCESS database to SQL Server. The DTS are created by saving the actual DTS
packages as VB file and used those bas files in VP app to run the DTS
programmatically.
The process works fine most of the time.
But occassionally the DTS process is getting locked. The two connections
from the DTS package to SQL server database gains DB lock on the database
and so the process does not go further .
Can any body know why this is happenning?
what could be the solution for this problem?
please help.
thanks
SamHow are you sure it's a deadlock instead of a normal blocking?
You can set up trace flag -T1204 and -T3605 in the start up parameters on
the SQL server and capture deadlock details in the SQL error log. Or if you
have lumigent Log Explorer, you can set up alert on deadlocks. YOu can also
use SQL profiler to capture a trace using Locks:Deadlocks and Lock:Deadlock
Chain.
Richard
"Sam" <samirsoni@.hotmail.com> wrote in message
news:elDzAt6MFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have created a VB program to perform DTS tasks for data transfer from
> ACCESS database to SQL Server. The DTS are created by saving the actual
> DTS
> packages as VB file and used those bas files in VP app to run the DTS
> programmatically.
> The process works fine most of the time.
> But occassionally the DTS process is getting locked. The two connections
> from the DTS package to SQL server database gains DB lock on the database
> and so the process does not go further .
> Can any body know why this is happenning?
> what could be the solution for this problem?
> please help.
> thanks
> Sam
>
>
deadlock
Hi,
I have created a VB program to perform DTS tasks for data transfer from
ACCESS database to SQL Server. The DTS are created by saving the actual DTS
packages as VB file and used those bas files in VP app to run the DTS
programmatically.
The process works fine most of the time.
But occassionally the DTS process is getting locked. The two connections
from the DTS package to SQL server database gains DB lock on the database
and so the process does not go further .
Can any body know why this is happenning?
what could be the solution for this problem?
please help.
thanks
SamHow are you sure it's a deadlock instead of a normal blocking?
You can set up trace flag -T1204 and -T3605 in the start up parameters on
the SQL server and capture deadlock details in the SQL error log. Or if you
have lumigent Log Explorer, you can set up alert on deadlocks. YOu can also
use SQL profiler to capture a trace using Locks:Deadlocks and Lock:Deadlock
Chain.
Richard
"Sam" <samirsoni@.hotmail.com> wrote in message
news:elDzAt6MFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have created a VB program to perform DTS tasks for data transfer from
> ACCESS database to SQL Server. The DTS are created by saving the actual
> DTS
> packages as VB file and used those bas files in VP app to run the DTS
> programmatically.
> The process works fine most of the time.
> But occassionally the DTS process is getting locked. The two connections
> from the DTS package to SQL server database gains DB lock on the database
> and so the process does not go further .
> Can any body know why this is happenning?
> what could be the solution for this problem?
> please help.
> thanks
> Sam
>
>
I have created a VB program to perform DTS tasks for data transfer from
ACCESS database to SQL Server. The DTS are created by saving the actual DTS
packages as VB file and used those bas files in VP app to run the DTS
programmatically.
The process works fine most of the time.
But occassionally the DTS process is getting locked. The two connections
from the DTS package to SQL server database gains DB lock on the database
and so the process does not go further .
Can any body know why this is happenning?
what could be the solution for this problem?
please help.
thanks
SamHow are you sure it's a deadlock instead of a normal blocking?
You can set up trace flag -T1204 and -T3605 in the start up parameters on
the SQL server and capture deadlock details in the SQL error log. Or if you
have lumigent Log Explorer, you can set up alert on deadlocks. YOu can also
use SQL profiler to capture a trace using Locks:Deadlocks and Lock:Deadlock
Chain.
Richard
"Sam" <samirsoni@.hotmail.com> wrote in message
news:elDzAt6MFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have created a VB program to perform DTS tasks for data transfer from
> ACCESS database to SQL Server. The DTS are created by saving the actual
> DTS
> packages as VB file and used those bas files in VP app to run the DTS
> programmatically.
> The process works fine most of the time.
> But occassionally the DTS process is getting locked. The two connections
> from the DTS package to SQL server database gains DB lock on the database
> and so the process does not go further .
> Can any body know why this is happenning?
> what could be the solution for this problem?
> please help.
> thanks
> Sam
>
>
Wednesday, March 7, 2012
DDL of an existing cube
Hi!
Is there a way to get the DDL of a cube created with the AS wizard.
Thinks and regards.
You can get the DDL of any AS object, including a cube, very easily by connecting to your server in SQL Server Management Studio, right-clicking on the object and selecting one of the items under the 'Script <object> as' menu item.
HTH,
Chris
|||Also, if you do not have the cube already deployed to AS2005, but you only have the BI project on which you created a cube with the wizard, you can right click on the cube item (in Visual Studio) and use 'View Code' option to see the XML.
Adrian Dumitrascu.
|||Hi
I think i've to install a full version of SQL Server 2k to get access to SQL Server Management Studio.
Actually i've installed AS only and i'm using an Oracle DB as DWH.
TVM Chris for reply
Regards
|||Hi
How Can i browse AS cubes within Visual Studio ( 6 ? .Net ? 2005?)
PS i'm using SQL2K AS
TVM for reply Adrian
Regards
|||If you have Analysis Services 2000, then our suggestions won't work. Analysis Services 2000 doesn't have DDL scripts as Analysis Services 2005.
SQL Management Studio only works with AS2005, but not AS2000 (Analysis Manager remains the admin client for AS2000).
The same for BI projects (that you can create and edit in Visual Studio): they are a feature of AS2005 only. So there isn't a feature out of the box to browse AS2000 from Visual Studio.
Adrian Dumitrascu.
DDL
In logshipping DDL could be transfer to standby server or not.
Like I have created one table after configuration the Logshipping so would
that table would be transfer in the standby server or not or I have to
create manually in the standby server.
Thanks
NOOR
> Like I have created one table after configuration the Logshipping so would
> that table would be transfer in the standby server or not or I have to
> create manually in the standby server.
As DDL actions are logged, they are executed on the standby server
automatically.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks
John
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%238jhZCTAFHA.2224@.TK2MSFTNGP14.phx.gbl...
> As DDL actions are logged, they are executed on the standby server
> automatically.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
Like I have created one table after configuration the Logshipping so would
that table would be transfer in the standby server or not or I have to
create manually in the standby server.
Thanks
NOOR
> Like I have created one table after configuration the Logshipping so would
> that table would be transfer in the standby server or not or I have to
> create manually in the standby server.
As DDL actions are logged, they are executed on the standby server
automatically.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks
John
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%238jhZCTAFHA.2224@.TK2MSFTNGP14.phx.gbl...
> As DDL actions are logged, they are executed on the standby server
> automatically.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
Saturday, February 25, 2012
DBTYPE_DBTIMESTAMP OLE DB question
Folks,
I am trying to use ole db to read a date field created as a DATETIME in C#.
It only seems to work if I set the binding type (wType) to
DBTYPE_DBTIMESTAMP. When I do this, 16 bytes of data are written to my
buffer. The question then is what to do with this raw data. What should I
cast it to, so that I can make use of it? I can't think of date/time types
that are 16 bytes long.
Thank you,
Matthew FlemingHi
The SQL Server data type "Timestamp" has nothing to do with date or time. It
is a binary number that is sequential and is generally used for concurrency
control in applications.
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/
"dermite" <dermite@.discussions.microsoft.com> wrote in message
news:614ADA8C-E424-4419-9FBC-B86C93646E80@.microsoft.com...
> Folks,
> I am trying to use ole db to read a date field created as a DATETIME in
> C#.
> It only seems to work if I set the binding type (wType) to
> DBTYPE_DBTIMESTAMP. When I do this, 16 bytes of data are written to my
> buffer. The question then is what to do with this raw data. What should I
> cast it to, so that I can make use of it? I can't think of date/time types
> that are 16 bytes long.
> Thank you,
> Matthew Fleming|||If this is so, then why is the field (which was created as type DATETIME),
readable only with a binding type of DBTYPE_DBTIMESTAMP? I tried
DBTYPE_DATE and it did not work (0 bytes were written to the buffer).
Matthew Fleming
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> The SQL Server data type "Timestamp" has nothing to do with date or time.
It
> is a binary number that is sequential and is generally used for concurrenc
y
> control in applications.
> 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/
> "dermite" <dermite@.discussions.microsoft.com> wrote in message
> news:614ADA8C-E424-4419-9FBC-B86C93646E80@.microsoft.com...
>
>|||Mike Epprecht (SQL MVP) (mike@.epprecht.net) writes:
> The SQL Server data type "Timestamp" has nothing to do with date or
> time. It is a binary number that is sequential and is generally used for
> concurrency control in applications.
Yes, but the OLE DB data type DBTYPE_DBTIMESTAMP has everything to do
with date and time. That is in fact how you get back the datetime data type
from SQL Server.
Anyway, the actual question have been sorted out in
microsoft.public.olddb.data. Please to do not post the same question to
different newsgroups independently!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
I am trying to use ole db to read a date field created as a DATETIME in C#.
It only seems to work if I set the binding type (wType) to
DBTYPE_DBTIMESTAMP. When I do this, 16 bytes of data are written to my
buffer. The question then is what to do with this raw data. What should I
cast it to, so that I can make use of it? I can't think of date/time types
that are 16 bytes long.
Thank you,
Matthew FlemingHi
The SQL Server data type "Timestamp" has nothing to do with date or time. It
is a binary number that is sequential and is generally used for concurrency
control in applications.
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/
"dermite" <dermite@.discussions.microsoft.com> wrote in message
news:614ADA8C-E424-4419-9FBC-B86C93646E80@.microsoft.com...
> Folks,
> I am trying to use ole db to read a date field created as a DATETIME in
> C#.
> It only seems to work if I set the binding type (wType) to
> DBTYPE_DBTIMESTAMP. When I do this, 16 bytes of data are written to my
> buffer. The question then is what to do with this raw data. What should I
> cast it to, so that I can make use of it? I can't think of date/time types
> that are 16 bytes long.
> Thank you,
> Matthew Fleming|||If this is so, then why is the field (which was created as type DATETIME),
readable only with a binding type of DBTYPE_DBTIMESTAMP? I tried
DBTYPE_DATE and it did not work (0 bytes were written to the buffer).
Matthew Fleming
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> The SQL Server data type "Timestamp" has nothing to do with date or time.
It
> is a binary number that is sequential and is generally used for concurrenc
y
> control in applications.
> 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/
> "dermite" <dermite@.discussions.microsoft.com> wrote in message
> news:614ADA8C-E424-4419-9FBC-B86C93646E80@.microsoft.com...
>
>|||Mike Epprecht (SQL MVP) (mike@.epprecht.net) writes:
> The SQL Server data type "Timestamp" has nothing to do with date or
> time. It is a binary number that is sequential and is generally used for
> concurrency control in applications.
Yes, but the OLE DB data type DBTYPE_DBTIMESTAMP has everything to do
with date and time. That is in fact how you get back the datetime data type
from SQL Server.
Anyway, the actual question have been sorted out in
microsoft.public.olddb.data. Please to do not post the same question to
different newsgroups independently!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Friday, February 24, 2012
dbo's Login Name is blank and can't be edited from Enterprise Manager
We are using SQL Server 2000 SP4. I just created a new database from the EM
but found that in the Users folder under this new db name the Login Name for
dbo was blank. I double-clicked the dbo line and it showed <None> in the
properities dialog box which could not be edited. Is it okay to exe
sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
Thanks,
Eli
> Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
Yes, sp_changedbowner will fix the database owner. I think it's odd that a
new database would have a NULL owner, though. I usually see that only when
the Windows account that was the database owner is deleted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2000 SP4. I just created a new database from the
> EM
> but found that in the Users folder under this new db name the Login Name
> for
> dbo was blank. I double-clicked the dbo line and it showed <None> in the
> properities dialog box which could not be edited. Is it okay to exe
> sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
> Thanks,
> Eli
>
|||Thanks Dan. It works. Appreciate your meesage.
Regards,
Eli
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> Yes, sp_changedbowner will fix the database owner. I think it's odd that
a
> new database would have a NULL owner, though. I usually see that only
when[vbcol=seagreen]
> the Windows account that was the database owner is deleted.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eli" <efeng@.kerisys.com> wrote in message
> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
idea?
>
|||I'm glad I was able to help. Thanks for taking the time to confirm.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:uZ80fLvIIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Thanks Dan. It works. Appreciate your meesage.
> Regards,
> Eli
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> a
> when
> idea?
>
but found that in the Users folder under this new db name the Login Name for
dbo was blank. I double-clicked the dbo line and it showed <None> in the
properities dialog box which could not be edited. Is it okay to exe
sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
Thanks,
Eli
> Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
Yes, sp_changedbowner will fix the database owner. I think it's odd that a
new database would have a NULL owner, though. I usually see that only when
the Windows account that was the database owner is deleted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2000 SP4. I just created a new database from the
> EM
> but found that in the Users folder under this new db name the Login Name
> for
> dbo was blank. I double-clicked the dbo line and it showed <None> in the
> properities dialog box which could not be edited. Is it okay to exe
> sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
> Thanks,
> Eli
>
|||Thanks Dan. It works. Appreciate your meesage.
Regards,
Eli
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> Yes, sp_changedbowner will fix the database owner. I think it's odd that
a
> new database would have a NULL owner, though. I usually see that only
when[vbcol=seagreen]
> the Windows account that was the database owner is deleted.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eli" <efeng@.kerisys.com> wrote in message
> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
idea?
>
|||I'm glad I was able to help. Thanks for taking the time to confirm.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:uZ80fLvIIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Thanks Dan. It works. Appreciate your meesage.
> Regards,
> Eli
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> a
> when
> idea?
>
dbo's Login Name is blank and can't be edited from Enterprise Manager
We are using SQL Server 2000 SP4. I just created a new database from the EM
but found that in the Users folder under this new db name the Login Name for
dbo was blank. I double-clicked the dbo line and it showed <None> in the
properities dialog box which could not be edited. Is it okay to exe
sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
Thanks,
Eli> Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
Yes, sp_changedbowner will fix the database owner. I think it's odd that a
new database would have a NULL owner, though. I usually see that only when
the Windows account that was the database owner is deleted.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2000 SP4. I just created a new database from the
> EM
> but found that in the Users folder under this new db name the Login Name
> for
> dbo was blank. I double-clicked the dbo line and it showed <None> in the
> properities dialog box which could not be edited. Is it okay to exe
> sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
> Thanks,
> Eli
>|||Thanks Dan. It works. Appreciate your meesage.
Regards,
Eli
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> > Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
> Yes, sp_changedbowner will fix the database owner. I think it's odd that
a
> new database would have a NULL owner, though. I usually see that only
when
> the Windows account that was the database owner is deleted.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eli" <efeng@.kerisys.com> wrote in message
> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> > We are using SQL Server 2000 SP4. I just created a new database from the
> > EM
> > but found that in the Users folder under this new db name the Login Name
> > for
> > dbo was blank. I double-clicked the dbo line and it showed <None> in the
> > properities dialog box which could not be edited. Is it okay to exe
> > sp_changedbowner 'sa' sepcially for this new database? Or any better
idea?
> > Thanks,
> > Eli
> >
> >
>|||I'm glad I was able to help. Thanks for taking the time to confirm.
--
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:uZ80fLvIIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Thanks Dan. It works. Appreciate your meesage.
> Regards,
> Eli
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
>> > Is it okay to exe sp_changedbowner 'sa' sepcially for this new
>> > database?
>> Yes, sp_changedbowner will fix the database owner. I think it's odd that
> a
>> new database would have a NULL owner, though. I usually see that only
> when
>> the Windows account that was the database owner is deleted.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Eli" <efeng@.kerisys.com> wrote in message
>> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> > We are using SQL Server 2000 SP4. I just created a new database from
>> > the
>> > EM
>> > but found that in the Users folder under this new db name the Login
>> > Name
>> > for
>> > dbo was blank. I double-clicked the dbo line and it showed <None> in
>> > the
>> > properities dialog box which could not be edited. Is it okay to exe
>> > sp_changedbowner 'sa' sepcially for this new database? Or any better
> idea?
>> > Thanks,
>> > Eli
>> >
>> >
>
but found that in the Users folder under this new db name the Login Name for
dbo was blank. I double-clicked the dbo line and it showed <None> in the
properities dialog box which could not be edited. Is it okay to exe
sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
Thanks,
Eli> Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
Yes, sp_changedbowner will fix the database owner. I think it's odd that a
new database would have a NULL owner, though. I usually see that only when
the Windows account that was the database owner is deleted.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2000 SP4. I just created a new database from the
> EM
> but found that in the Users folder under this new db name the Login Name
> for
> dbo was blank. I double-clicked the dbo line and it showed <None> in the
> properities dialog box which could not be edited. Is it okay to exe
> sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
> Thanks,
> Eli
>|||Thanks Dan. It works. Appreciate your meesage.
Regards,
Eli
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> > Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
> Yes, sp_changedbowner will fix the database owner. I think it's odd that
a
> new database would have a NULL owner, though. I usually see that only
when
> the Windows account that was the database owner is deleted.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eli" <efeng@.kerisys.com> wrote in message
> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> > We are using SQL Server 2000 SP4. I just created a new database from the
> > EM
> > but found that in the Users folder under this new db name the Login Name
> > for
> > dbo was blank. I double-clicked the dbo line and it showed <None> in the
> > properities dialog box which could not be edited. Is it okay to exe
> > sp_changedbowner 'sa' sepcially for this new database? Or any better
idea?
> > Thanks,
> > Eli
> >
> >
>|||I'm glad I was able to help. Thanks for taking the time to confirm.
--
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:uZ80fLvIIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Thanks Dan. It works. Appreciate your meesage.
> Regards,
> Eli
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
>> > Is it okay to exe sp_changedbowner 'sa' sepcially for this new
>> > database?
>> Yes, sp_changedbowner will fix the database owner. I think it's odd that
> a
>> new database would have a NULL owner, though. I usually see that only
> when
>> the Windows account that was the database owner is deleted.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Eli" <efeng@.kerisys.com> wrote in message
>> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> > We are using SQL Server 2000 SP4. I just created a new database from
>> > the
>> > EM
>> > but found that in the Users folder under this new db name the Login
>> > Name
>> > for
>> > dbo was blank. I double-clicked the dbo line and it showed <None> in
>> > the
>> > properities dialog box which could not be edited. Is it okay to exe
>> > sp_changedbowner 'sa' sepcially for this new database? Or any better
> idea?
>> > Thanks,
>> > Eli
>> >
>> >
>
dbo's Login Name is blank and can't be edited from Enterprise Manager
We are using SQL Server 2000 SP4. I just created a new database from the EM
but found that in the Users folder under this new db name the Login Name for
dbo was blank. I double-clicked the dbo line and it showed <None> in the
properities dialog box which could not be edited. Is it okay to exe
sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
Thanks,
Eli> Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
Yes, sp_changedbowner will fix the database owner. I think it's odd that a
new database would have a NULL owner, though. I usually see that only when
the Windows account that was the database owner is deleted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2000 SP4. I just created a new database from the
> EM
> but found that in the Users folder under this new db name the Login Name
> for
> dbo was blank. I double-clicked the dbo line and it showed <None> in the
> properities dialog box which could not be edited. Is it okay to exe
> sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
> Thanks,
> Eli
>|||Thanks Dan. It works. Appreciate your meesage.
Regards,
Eli
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> Yes, sp_changedbowner will fix the database owner. I think it's odd that
a
> new database would have a NULL owner, though. I usually see that only
when
> the Windows account that was the database owner is deleted.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eli" <efeng@.kerisys.com> wrote in message
> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
idea?[vbcol=seagreen]
>|||I'm glad I was able to help. Thanks for taking the time to confirm.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:uZ80fLvIIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Thanks Dan. It works. Appreciate your meesage.
> Regards,
> Eli
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> a
> when
> idea?
>
but found that in the Users folder under this new db name the Login Name for
dbo was blank. I double-clicked the dbo line and it showed <None> in the
properities dialog box which could not be edited. Is it okay to exe
sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
Thanks,
Eli> Is it okay to exe sp_changedbowner 'sa' sepcially for this new database?
Yes, sp_changedbowner will fix the database owner. I think it's odd that a
new database would have a NULL owner, though. I usually see that only when
the Windows account that was the database owner is deleted.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> We are using SQL Server 2000 SP4. I just created a new database from the
> EM
> but found that in the Users folder under this new db name the Login Name
> for
> dbo was blank. I double-clicked the dbo line and it showed <None> in the
> properities dialog box which could not be edited. Is it okay to exe
> sp_changedbowner 'sa' sepcially for this new database? Or any better idea?
> Thanks,
> Eli
>|||Thanks Dan. It works. Appreciate your meesage.
Regards,
Eli
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> Yes, sp_changedbowner will fix the database owner. I think it's odd that
a
> new database would have a NULL owner, though. I usually see that only
when
> the Windows account that was the database owner is deleted.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eli" <efeng@.kerisys.com> wrote in message
> news:%233Pa1LmIIHA.4196@.TK2MSFTNGP04.phx.gbl...
idea?[vbcol=seagreen]
>|||I'm glad I was able to help. Thanks for taking the time to confirm.
Dan Guzman
SQL Server MVP
"Eli" <efeng@.kerisys.com> wrote in message
news:uZ80fLvIIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Thanks Dan. It works. Appreciate your meesage.
> Regards,
> Eli
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:8849D3CB-78AA-42C8-8AA5-9E646991CDFC@.microsoft.com...
> a
> when
> idea?
>
Tuesday, February 14, 2012
Dblocks with temporary table
Hi,
I am using temp table in my stored procedure. The temp table is first created and rows are inserted. Then I am selecting data from same temp table. Everything is inside the stored procedure. This stored procedure is called from a Java (EJB) program with at least 100 threads at the same time.
But when the Java program runs and calls this stored procedure, it is resulting in many dblocks. Can any one explain why this is happening ? I appreciate any help on this.
Does each thread create its own copy of temp table ?
Is temdb locked in this process ?
Do I need to drop the tamp table at the end ? ( I am not dropping now)
What are the other alternatives ?
I am on SQL Server 7, windows 2000/NT.
Thanx..
-BheemsenThe temp table is first created and rows are inserted...
Which approach do you use?
1. select into
2. create table + insert
Select into locks system tables in MSSQL7.|||Thanx ispaleny. I am using the 2 option.
i.e. create table, then insert, then select.
-Bheemsen|||I use MSSQL2k.
#tables are created unique for each thread
#tables created in scope of SP exist only in scope of SP|||RE:
Thanx ispaleny. I am using option 2. (create table, then insert). -Bheemsen
Question I
Since it isn't a simple select into locking issue, what are the server's wait states like when the issue presents itself? Also, what kinds of locks are being issued and in what proportion, and is the Java app possibly spawning multiple connections (rather than reusing when possible) and / or not closing out connections when done with them?
wait states data gathering example:
Select
Spid,
Waittime,
Lastwaittype,
Waitresource
From
Master..Sysprocesses
Where
Waittime > 300
I am using temp table in my stored procedure. The temp table is first created and rows are inserted. Then I am selecting data from same temp table. Everything is inside the stored procedure. This stored procedure is called from a Java (EJB) program with at least 100 threads at the same time.
But when the Java program runs and calls this stored procedure, it is resulting in many dblocks. Can any one explain why this is happening ? I appreciate any help on this.
Does each thread create its own copy of temp table ?
Is temdb locked in this process ?
Do I need to drop the tamp table at the end ? ( I am not dropping now)
What are the other alternatives ?
I am on SQL Server 7, windows 2000/NT.
Thanx..
-BheemsenThe temp table is first created and rows are inserted...
Which approach do you use?
1. select into
2. create table + insert
Select into locks system tables in MSSQL7.|||Thanx ispaleny. I am using the 2 option.
i.e. create table, then insert, then select.
-Bheemsen|||I use MSSQL2k.
#tables are created unique for each thread
#tables created in scope of SP exist only in scope of SP|||RE:
Thanx ispaleny. I am using option 2. (create table, then insert). -Bheemsen
Question I
Since it isn't a simple select into locking issue, what are the server's wait states like when the issue presents itself? Also, what kinds of locks are being issued and in what proportion, and is the Java app possibly spawning multiple connections (rather than reusing when possible) and / or not closing out connections when done with them?
wait states data gathering example:
Select
Spid,
Waittime,
Lastwaittype,
Waitresource
From
Master..Sysprocesses
Where
Waittime > 300
Subscribe to:
Posts (Atom)