I have a sitation where I need a dts package or similar to run at a user initiated time. I do not want to give the user access to the server. Any ideas on how one goes about something like this?Howdy
If its through a web or VB type app, let the web app execute a stored procedure called by the app.
Cheers,
SG.|||Hey,
from a vb app or vb script you can do this function:
Public Sub ExecuteEDIPackage(FileName As Variant)
Dim sServer As String
Dim sUsername As String
Dim sPassword As String
Dim sPackageName As String
Dim lErr As Long
Dim sSource As String
Dim sDesc As String
Set oPKG = New DTS.Package
' Set Parameter Values
sPackageName = "EDIPackage"
' Load Package
oPKG.LoadFromSQLServer DataSource, UserName, Password, _
DTSSQLStgFlag_Default, , , , sPackageName
' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next
' Execute
oPKG.Execute
' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next
oPKG.UnInitialize
Set oStep = Nothing
Set oPKG = Nothing
End Sub
Showing posts with label time. Show all posts
Showing posts with label time. Show all posts
Thursday, March 29, 2012
Exec time for a query to run in QA?
Hi All
I want to see how long it takes for my query to execute in QA. How do i do
that? Thanx in advanceThere is an Execution Time element at the lower right hand part of the
screen in QA. Optionally you could turn on Client Statistics, Statistics
Time or using GETDATE() before and after your query.
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:00E0F137-A60C-44F6-964B-23C0D27A89C9@.microsoft.com...
> Hi All
> I want to see how long it takes for my query to execute in QA. How do i do
> that? Thanx in advance|||When it's done, look in the lower right corner of the status bar, the third
box from the right shows elapsed time.
You can also do another tactic, like
SELECT @.dt = CURRENT_TIMESTAMP
-- query here
SELECT DATEDIFF(MS, @.dt, CURRENT_TIMESTAMP)
You can also look at SET STATISTICS TIME and SET STATISTICS IO topics in
books online to see how to return different stats about the query or
queries. Showing execution plan and server/client statictics can also be
useful.
http://www.aspfaq.com/2245
A
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:00E0F137-A60C-44F6-964B-23C0D27A89C9@.microsoft.com...
> Hi All
> I want to see how long it takes for my query to execute in QA. How do i do
> that? Thanx in advance|||MittyKom wrote:
> Hi All
> I want to see how long it takes for my query to execute in QA. How do
> i do that? Thanx in advance
Best way is to use Profiler. But you can use SET STATISTICS TIME ON /
OFF from Query Analyzer to see the execution. STATISTICS IO is also
useful. Try this:
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT * FROM pubs.dbo.authors
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
David Gugick
Quest Software
www.imceda.com
www.quest.com
I want to see how long it takes for my query to execute in QA. How do i do
that? Thanx in advanceThere is an Execution Time element at the lower right hand part of the
screen in QA. Optionally you could turn on Client Statistics, Statistics
Time or using GETDATE() before and after your query.
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:00E0F137-A60C-44F6-964B-23C0D27A89C9@.microsoft.com...
> Hi All
> I want to see how long it takes for my query to execute in QA. How do i do
> that? Thanx in advance|||When it's done, look in the lower right corner of the status bar, the third
box from the right shows elapsed time.
You can also do another tactic, like
SELECT @.dt = CURRENT_TIMESTAMP
-- query here
SELECT DATEDIFF(MS, @.dt, CURRENT_TIMESTAMP)
You can also look at SET STATISTICS TIME and SET STATISTICS IO topics in
books online to see how to return different stats about the query or
queries. Showing execution plan and server/client statictics can also be
useful.
http://www.aspfaq.com/2245
A
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:00E0F137-A60C-44F6-964B-23C0D27A89C9@.microsoft.com...
> Hi All
> I want to see how long it takes for my query to execute in QA. How do i do
> that? Thanx in advance|||MittyKom wrote:
> Hi All
> I want to see how long it takes for my query to execute in QA. How do
> i do that? Thanx in advance
Best way is to use Profiler. But you can use SET STATISTICS TIME ON /
OFF from Query Analyzer to see the execution. STATISTICS IO is also
useful. Try this:
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT * FROM pubs.dbo.authors
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
David Gugick
Quest Software
www.imceda.com
www.quest.com
Monday, March 26, 2012
EXEC or EXECUTE, SET or SELECT
Hi
pls find time to throw some light on
whether and where to use EXE/EXECUTE
and SET/SELECT?
What are the differences?
Another thing..if we start a trigger
and within the transaction statements
we first truncate any table and then
call rollback, will that table be
rolled back or not? What will be the
end result?
thanks in advance
Suresh Beniwal- SET
To assign a value to a variable
- SELECT vs SET
SELECT let you assign a value to multiple variables in the same statement.
Example:
declare @.d datetime
declare @.i int
select @.d = getdate(), @.i = @.@.error
- EXEC and EXECUTE
To execute a sp, both are the same because it if enough with the first four
letters.
execute sp_who2
exec sp_who2
To execute a string you use:
exec (string_var)
- begin transaction truncate table table_name rollback transaction
the table will be exactly that it was before the transaction.
AMB
"SureshBeniwal" wrote:
> Hi
> pls find time to throw some light on
> whether and where to use EXE/EXECUTE
> and SET/SELECT?
> What are the differences?
> Another thing..if we start a trigger
> and within the transaction statements
> we first truncate any table and then
> call rollback, will that table be
> rolled back or not? What will be the
> end result?
> thanks in advance
> Suresh Beniwal
>|||SET can only assign one value to a local variable. SELECT can assign more
than one. Other than that, there's no difference (as far as assigning local
variables is concerned). If you're saving the values of both @.@.ROWCOUNT and
@.@.ERROR, then you should definitely use SELECT because these are changed by
every statement.
EXEC is shorthand for EXECUTE.
Truncate table is a logged operation--even though it is minimally logged.
If it is executed within a transaction, a rollback will undo it.
"SureshBeniwal" <suresh.beniwal@.gmail.com> wrote in message
news:1128003680.461221.32320@.g14g2000cwa.googlegroups.com...
> Hi
> pls find time to throw some light on
> whether and where to use EXE/EXECUTE
> and SET/SELECT?
> What are the differences?
> Another thing..if we start a trigger
> and within the transaction statements
> we first truncate any table and then
> call rollback, will that table be
> rolled back or not? What will be the
> end result?
> thanks in advance
> Suresh Beniwal
>|||"SureshBeniwal" <suresh.beniwal@.gmail.com> wrote in message news:1128003680.461221.32320@.g1
4g2000cwa.googlegroups.com...
> Hi
> pls find time to throw some light on
> whether and where to use EXE/EXECUTE
There is no difference betweeen EXEC and EXECUTE. EXEC is just an allowed a
bbreviation.
EXE however does nothing.
> and SET/SELECT?
> What are the differences?
Set is specifically used for setting variables, select can be used that way
,
but is more normally for entire result sets, and returns them to the client
I always use SET where they both work
> Another thing..if we start a trigger
> and within the transaction statements
> we first truncate any table and then
> call rollback, will that table be
> rolled back or not?
Yes. Until it's committed truncate is undoable. It is faster / more effici
ent because
it writes less to the transaction log, but it is still transactionable !
30 sec test:
create table Test ( SomeID int identity, SomeOthercol varchar(20) )
go
insert Test ( SomeOtherCol) values ('One')
insert Test ( SomeOtherCol) values ('Two')
insert Test ( SomeOtherCol) values ('Three')
insert Test ( SomeOtherCol) values ('Four')
insert Test ( SomeOtherCol) values ('Five')
go
begin tran
delete Test
select * from test
rollback tran
select * from test
begin tran
truncate table Test
select * from Test
rollback tran
select * from Test
What will be the
> end result?
> thanks in advance
> Suresh Beniwal
>
Regards
AJ|||Hi All,
Thanks a lot for those basic concepts
Regards,
Suresh Beniwal
pls find time to throw some light on
whether and where to use EXE/EXECUTE
and SET/SELECT?
What are the differences?
Another thing..if we start a trigger
and within the transaction statements
we first truncate any table and then
call rollback, will that table be
rolled back or not? What will be the
end result?
thanks in advance
Suresh Beniwal- SET
To assign a value to a variable
- SELECT vs SET
SELECT let you assign a value to multiple variables in the same statement.
Example:
declare @.d datetime
declare @.i int
select @.d = getdate(), @.i = @.@.error
- EXEC and EXECUTE
To execute a sp, both are the same because it if enough with the first four
letters.
execute sp_who2
exec sp_who2
To execute a string you use:
exec (string_var)
- begin transaction truncate table table_name rollback transaction
the table will be exactly that it was before the transaction.
AMB
"SureshBeniwal" wrote:
> Hi
> pls find time to throw some light on
> whether and where to use EXE/EXECUTE
> and SET/SELECT?
> What are the differences?
> Another thing..if we start a trigger
> and within the transaction statements
> we first truncate any table and then
> call rollback, will that table be
> rolled back or not? What will be the
> end result?
> thanks in advance
> Suresh Beniwal
>|||SET can only assign one value to a local variable. SELECT can assign more
than one. Other than that, there's no difference (as far as assigning local
variables is concerned). If you're saving the values of both @.@.ROWCOUNT and
@.@.ERROR, then you should definitely use SELECT because these are changed by
every statement.
EXEC is shorthand for EXECUTE.
Truncate table is a logged operation--even though it is minimally logged.
If it is executed within a transaction, a rollback will undo it.
"SureshBeniwal" <suresh.beniwal@.gmail.com> wrote in message
news:1128003680.461221.32320@.g14g2000cwa.googlegroups.com...
> Hi
> pls find time to throw some light on
> whether and where to use EXE/EXECUTE
> and SET/SELECT?
> What are the differences?
> Another thing..if we start a trigger
> and within the transaction statements
> we first truncate any table and then
> call rollback, will that table be
> rolled back or not? What will be the
> end result?
> thanks in advance
> Suresh Beniwal
>|||"SureshBeniwal" <suresh.beniwal@.gmail.com> wrote in message news:1128003680.461221.32320@.g1
4g2000cwa.googlegroups.com...
> Hi
> pls find time to throw some light on
> whether and where to use EXE/EXECUTE
There is no difference betweeen EXEC and EXECUTE. EXEC is just an allowed a
bbreviation.
EXE however does nothing.
> and SET/SELECT?
> What are the differences?
Set is specifically used for setting variables, select can be used that way
,
but is more normally for entire result sets, and returns them to the client
I always use SET where they both work
> Another thing..if we start a trigger
> and within the transaction statements
> we first truncate any table and then
> call rollback, will that table be
> rolled back or not?
Yes. Until it's committed truncate is undoable. It is faster / more effici
ent because
it writes less to the transaction log, but it is still transactionable !
30 sec test:
create table Test ( SomeID int identity, SomeOthercol varchar(20) )
go
insert Test ( SomeOtherCol) values ('One')
insert Test ( SomeOtherCol) values ('Two')
insert Test ( SomeOtherCol) values ('Three')
insert Test ( SomeOtherCol) values ('Four')
insert Test ( SomeOtherCol) values ('Five')
go
begin tran
delete Test
select * from test
rollback tran
select * from test
begin tran
truncate table Test
select * from Test
rollback tran
select * from Test
What will be the
> end result?
> thanks in advance
> Suresh Beniwal
>
Regards
AJ|||Hi All,
Thanks a lot for those basic concepts
Regards,
Suresh Beniwal
EXEC Command in all existing connections
If there any way to execute t-sql command in all connections in one time.
Example : if user insert a new record in table employees I like to notify
all users that are connected.
Aleksandar TalevYou could create a Trigger on the table for INSERT.
Have the Trigger fire the following :-
net send /users "New record added to table."
the /users switch will broadcast to all users connected to the server.
HTH
Ryan Waight, MCDBA, MCSE
"Aleksandar Talev" <alex@.semos.com.mk> wrote in message
news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> If there any way to execute t-sql command in all connections in one time.
>
> Example : if user insert a new record in table employees I like to notify
> all users that are connected.
>
> Aleksandar Talev
>
>|||This is very helpfull
Thanks.
I also like to know can I substitute net send command with osql or bcp
(including also all users) ?
AT
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:#ShJRoFqDHA.1124@.TK2MSFTNGP09.phx.gbl...
> You could create a Trigger on the table for INSERT.
> Have the Trigger fire the following :-
> net send /users "New record added to table."
> the /users switch will broadcast to all users connected to the server.
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Aleksandar Talev" <alex@.semos.com.mk> wrote in message
> news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > If there any way to execute t-sql command in all connections in one
time.
> >
> >
> > Example : if user insert a new record in table employees I like to
notify
> > all users that are connected.
> >
> >
> > Aleksandar Talev
> >
> >
> >
> >
>
Example : if user insert a new record in table employees I like to notify
all users that are connected.
Aleksandar TalevYou could create a Trigger on the table for INSERT.
Have the Trigger fire the following :-
net send /users "New record added to table."
the /users switch will broadcast to all users connected to the server.
HTH
Ryan Waight, MCDBA, MCSE
"Aleksandar Talev" <alex@.semos.com.mk> wrote in message
news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> If there any way to execute t-sql command in all connections in one time.
>
> Example : if user insert a new record in table employees I like to notify
> all users that are connected.
>
> Aleksandar Talev
>
>|||This is very helpfull
Thanks.
I also like to know can I substitute net send command with osql or bcp
(including also all users) ?
AT
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:#ShJRoFqDHA.1124@.TK2MSFTNGP09.phx.gbl...
> You could create a Trigger on the table for INSERT.
> Have the Trigger fire the following :-
> net send /users "New record added to table."
> the /users switch will broadcast to all users connected to the server.
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Aleksandar Talev" <alex@.semos.com.mk> wrote in message
> news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > If there any way to execute t-sql command in all connections in one
time.
> >
> >
> > Example : if user insert a new record in table employees I like to
notify
> > all users that are connected.
> >
> >
> > Aleksandar Talev
> >
> >
> >
> >
>
Thursday, March 22, 2012
Exclusive DB access
Hi,
I'm searching a way or a method to gain exclusive access for a short time
period (minutes) to a SQL 2000 database from a VB application using ADO and
SQLDMO, in order to do some data historization and computation, and
preventing that others users may connect to the database during this time.
After searching in the SQLDMO object model, I couldn't find anything adequate
for this purpose. Is the only way to lock/unlock all the tables ?
Has anyone an idea ?
RobertoAnswered in .connectivity
Please do not multi-post.
Regards
Mike
"GunSmoke_62" wrote:
> Hi,
> I'm searching a way or a method to gain exclusive access for a short time
> period (minutes) to a SQL 2000 database from a VB application using ADO and
> SQLDMO, in order to do some data historization and computation, and
> preventing that others users may connect to the database during this time.
> After searching in the SQLDMO object model, I couldn't find anything adequate
> for this purpose. Is the only way to lock/unlock all the tables ?
> Has anyone an idea ?
> Roberto
I'm searching a way or a method to gain exclusive access for a short time
period (minutes) to a SQL 2000 database from a VB application using ADO and
SQLDMO, in order to do some data historization and computation, and
preventing that others users may connect to the database during this time.
After searching in the SQLDMO object model, I couldn't find anything adequate
for this purpose. Is the only way to lock/unlock all the tables ?
Has anyone an idea ?
RobertoAnswered in .connectivity
Please do not multi-post.
Regards
Mike
"GunSmoke_62" wrote:
> Hi,
> I'm searching a way or a method to gain exclusive access for a short time
> period (minutes) to a SQL 2000 database from a VB application using ADO and
> SQLDMO, in order to do some data historization and computation, and
> preventing that others users may connect to the database during this time.
> After searching in the SQLDMO object model, I couldn't find anything adequate
> for this purpose. Is the only way to lock/unlock all the tables ?
> Has anyone an idea ?
> Roberto
Exclusive access to MS SQL Database
Hi,
I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
application through ADO for a very short period of time in order to do
special processing (data historization and computation) and preventing that
others users can connect at the same time. Which is the best method ?
Best regards.
Roberto
Hi
Kill all the other connections where SPID > 50, except for yours. Put the DB
in Single Use mode, do your processing and then remove Single Use Mode.
Regards
Mike
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing that
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto
|||use ALTER DATABASE,
Example:
use master
go
alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go
--do your stuff
go
alter database northwind
set MULTI_USER with NO_WAIT
go
AMB
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing that
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto
|||Your application will need to login with system admin rights in order to do
the following:
Kill all connections automatically (no need to kill each indicidually),
rollback any unresolved transactions, and place the database in single user
mode.
ALTER DATABASE API SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Leave the database in read-only mode. This could help optimize performace of
reporting and data transfer processing.
ALTER DATABASE API SET READ_ONLY
Restrict login to the database to only those users belonging to the database
owner (DBO) role.
ALTER DATABASE API SET RESTRICTED_USER
After doing your thing, the following will restore the database back to
normal.
ALTER DATABASE API SET READ_WRITE
ALTER DATABASE API SET MULTI_USER
Also, for periodic reporting, you may want to restore the latest backup to a
dedicated reporting server or database. That way, the production database
would not need to be made unavailable.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:5E794404-2B42-4EDB-A8AC-E65B39D234B3@.microsoft.com...
> Hi
> Kill all the other connections where SPID > 50, except for yours. Put the
DB[vbcol=seagreen]
> in Single Use mode, do your processing and then remove Single Use Mode.
> Regards
> Mike
> "GunSmoke_62" wrote:
VB[vbcol=seagreen]
that[vbcol=seagreen]
sql
I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
application through ADO for a very short period of time in order to do
special processing (data historization and computation) and preventing that
others users can connect at the same time. Which is the best method ?
Best regards.
Roberto
Hi
Kill all the other connections where SPID > 50, except for yours. Put the DB
in Single Use mode, do your processing and then remove Single Use Mode.
Regards
Mike
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing that
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto
|||use ALTER DATABASE,
Example:
use master
go
alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go
--do your stuff
go
alter database northwind
set MULTI_USER with NO_WAIT
go
AMB
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing that
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto
|||Your application will need to login with system admin rights in order to do
the following:
Kill all connections automatically (no need to kill each indicidually),
rollback any unresolved transactions, and place the database in single user
mode.
ALTER DATABASE API SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Leave the database in read-only mode. This could help optimize performace of
reporting and data transfer processing.
ALTER DATABASE API SET READ_ONLY
Restrict login to the database to only those users belonging to the database
owner (DBO) role.
ALTER DATABASE API SET RESTRICTED_USER
After doing your thing, the following will restore the database back to
normal.
ALTER DATABASE API SET READ_WRITE
ALTER DATABASE API SET MULTI_USER
Also, for periodic reporting, you may want to restore the latest backup to a
dedicated reporting server or database. That way, the production database
would not need to be made unavailable.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:5E794404-2B42-4EDB-A8AC-E65B39D234B3@.microsoft.com...
> Hi
> Kill all the other connections where SPID > 50, except for yours. Put the
DB[vbcol=seagreen]
> in Single Use mode, do your processing and then remove Single Use Mode.
> Regards
> Mike
> "GunSmoke_62" wrote:
VB[vbcol=seagreen]
that[vbcol=seagreen]
sql
Wednesday, March 21, 2012
Exclusive access to MS SQL Database
Hi,
I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
application through ADO for a very short period of time in order to do
special processing (data historization and computation) and preventing that
others users can connect at the same time. Which is the best method ?
Best regards.
RobertoHi
Kill all the other connections where SPID > 50, except for yours. Put the DB
in Single Use mode, do your processing and then remove Single Use Mode.
Regards
Mike
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing tha
t
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto|||use ALTER DATABASE,
Example:
use master
go
alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go
--do your stuff
go
alter database northwind
set MULTI_USER with NO_WAIT
go
AMB
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing tha
t
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto|||Your application will need to login with system admin rights in order to do
the following:
Kill all connections automatically (no need to kill each indicidually),
rollback any unresolved transactions, and place the database in single user
mode.
ALTER DATABASE API SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Leave the database in read-only mode. This could help optimize performace of
reporting and data transfer processing.
ALTER DATABASE API SET READ_ONLY
Restrict login to the database to only those users belonging to the database
owner (DBO) role.
ALTER DATABASE API SET RESTRICTED_USER
After doing your thing, the following will restore the database back to
normal.
ALTER DATABASE API SET READ_WRITE
ALTER DATABASE API SET MULTI_USER
Also, for periodic reporting, you may want to restore the latest backup to a
dedicated reporting server or database. That way, the production database
would not need to be made unavailable.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:5E794404-2B42-4EDB-A8AC-E65B39D234B3@.microsoft.com...
> Hi
> Kill all the other connections where SPID > 50, except for yours. Put the
DB[vbcol=seagreen]
> in Single Use mode, do your processing and then remove Single Use Mode.
> Regards
> Mike
> "GunSmoke_62" wrote:
>
VB[vbcol=seagreen]
that[vbcol=seagreen]
I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
application through ADO for a very short period of time in order to do
special processing (data historization and computation) and preventing that
others users can connect at the same time. Which is the best method ?
Best regards.
RobertoHi
Kill all the other connections where SPID > 50, except for yours. Put the DB
in Single Use mode, do your processing and then remove Single Use Mode.
Regards
Mike
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing tha
t
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto|||use ALTER DATABASE,
Example:
use master
go
alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go
--do your stuff
go
alter database northwind
set MULTI_USER with NO_WAIT
go
AMB
"GunSmoke_62" wrote:
> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing tha
t
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto|||Your application will need to login with system admin rights in order to do
the following:
Kill all connections automatically (no need to kill each indicidually),
rollback any unresolved transactions, and place the database in single user
mode.
ALTER DATABASE API SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Leave the database in read-only mode. This could help optimize performace of
reporting and data transfer processing.
ALTER DATABASE API SET READ_ONLY
Restrict login to the database to only those users belonging to the database
owner (DBO) role.
ALTER DATABASE API SET RESTRICTED_USER
After doing your thing, the following will restore the database back to
normal.
ALTER DATABASE API SET READ_WRITE
ALTER DATABASE API SET MULTI_USER
Also, for periodic reporting, you may want to restore the latest backup to a
dedicated reporting server or database. That way, the production database
would not need to be made unavailable.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:5E794404-2B42-4EDB-A8AC-E65B39D234B3@.microsoft.com...
> Hi
> Kill all the other connections where SPID > 50, except for yours. Put the
DB[vbcol=seagreen]
> in Single Use mode, do your processing and then remove Single Use Mode.
> Regards
> Mike
> "GunSmoke_62" wrote:
>
VB[vbcol=seagreen]
that[vbcol=seagreen]
Exclude system IDs in 2005?
Im trying out Profiler in 2005 for my first time, but don't see a way to
exclude system IDs as was possible in 2000 on the Filters tab. Is this
possible?
TIA, ChrisRHi Chris
System objects are managed completely differently in SQL Server 2005, which
is why I imagine they removed that option.
What data column are you trying to filter, for what types of objects?
You can just use the not like filter to exclude names you're not interested
in.
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <NotAChance@.ms.com> wrote in message
news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
> Im trying out Profiler in 2005 for my first time, but don't see a way to
> exclude system IDs as was possible in 2000 on the Filters tab. Is this
> possible?
> TIA, ChrisR
>|||Thanks Kalen.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OuvE090sGHA.372@.TK2MSFTNGP06.phx.gbl...
> Hi Chris
> System objects are managed completely differently in SQL Server 2005,
which
> is why I imagine they removed that option.
> What data column are you trying to filter, for what types of objects?
> You can just use the not like filter to exclude names you're not
interested
> in.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
>
exclude system IDs as was possible in 2000 on the Filters tab. Is this
possible?
TIA, ChrisRHi Chris
System objects are managed completely differently in SQL Server 2005, which
is why I imagine they removed that option.
What data column are you trying to filter, for what types of objects?
You can just use the not like filter to exclude names you're not interested
in.
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <NotAChance@.ms.com> wrote in message
news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
> Im trying out Profiler in 2005 for my first time, but don't see a way to
> exclude system IDs as was possible in 2000 on the Filters tab. Is this
> possible?
> TIA, ChrisR
>|||Thanks Kalen.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OuvE090sGHA.372@.TK2MSFTNGP06.phx.gbl...
> Hi Chris
> System objects are managed completely differently in SQL Server 2005,
which
> is why I imagine they removed that option.
> What data column are you trying to filter, for what types of objects?
> You can just use the not like filter to exclude names you're not
interested
> in.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
>
Exclude system IDs in 2005?
Im trying out Profiler in 2005 for my first time, but don't see a way to
exclude system IDs as was possible in 2000 on the Filters tab. Is this
possible?
TIA, ChrisRHi Chris
System objects are managed completely differently in SQL Server 2005, which
is why I imagine they removed that option.
What data column are you trying to filter, for what types of objects?
You can just use the not like filter to exclude names you're not interested
in.
--
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <NotAChance@.ms.com> wrote in message
news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
> Im trying out Profiler in 2005 for my first time, but don't see a way to
> exclude system IDs as was possible in 2000 on the Filters tab. Is this
> possible?
> TIA, ChrisR
>|||Thanks Kalen.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OuvE090sGHA.372@.TK2MSFTNGP06.phx.gbl...
> Hi Chris
> System objects are managed completely differently in SQL Server 2005,
which
> is why I imagine they removed that option.
> What data column are you trying to filter, for what types of objects?
> You can just use the not like filter to exclude names you're not
interested
> in.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
> > Im trying out Profiler in 2005 for my first time, but don't see a way to
> > exclude system IDs as was possible in 2000 on the Filters tab. Is this
> > possible?
> >
> > TIA, ChrisR
> >
> >
>sql
exclude system IDs as was possible in 2000 on the Filters tab. Is this
possible?
TIA, ChrisRHi Chris
System objects are managed completely differently in SQL Server 2005, which
is why I imagine they removed that option.
What data column are you trying to filter, for what types of objects?
You can just use the not like filter to exclude names you're not interested
in.
--
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <NotAChance@.ms.com> wrote in message
news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
> Im trying out Profiler in 2005 for my first time, but don't see a way to
> exclude system IDs as was possible in 2000 on the Filters tab. Is this
> possible?
> TIA, ChrisR
>|||Thanks Kalen.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OuvE090sGHA.372@.TK2MSFTNGP06.phx.gbl...
> Hi Chris
> System objects are managed completely differently in SQL Server 2005,
which
> is why I imagine they removed that option.
> What data column are you trying to filter, for what types of objects?
> You can just use the not like filter to exclude names you're not
interested
> in.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
> > Im trying out Profiler in 2005 for my first time, but don't see a way to
> > exclude system IDs as was possible in 2000 on the Filters tab. Is this
> > possible?
> >
> > TIA, ChrisR
> >
> >
>sql
Friday, March 9, 2012
Excesive Space Allocated
Hello,
I have a fairly large database that at one time I used to do a lot of processing on. Now it's nothing but backup for some old data. I've truncated my transaction log and it's down to about 25 megs. The problem is the transaction log is allocated for ar
ound 2.5 gigs and I can't get it to allocated any less so I'm waiting a ton of space on my server. Is there anyway to shrink how much size your allocated for your log file?
Thanks
Check out DBCC SHRINKFILE in BOL.
Andrew J. Kelly SQL MVP
"Chuck" <anonymous@.discussions.microsoft.com> wrote in message
news:9DF0AAF1-1776-4C66-BE74-EE649A3F7CFE@.microsoft.com...
> Hello,
> I have a fairly large database that at one time I used to do a lot of
processing on. Now it's nothing but backup for some old data. I've
truncated my transaction log and it's down to about 25 megs. The problem is
the transaction log is allocated for around 2.5 gigs and I can't get it to
allocated any less so I'm waiting a ton of space on my server. Is there
anyway to shrink how much size your allocated for your log file?
> Thanks
I have a fairly large database that at one time I used to do a lot of processing on. Now it's nothing but backup for some old data. I've truncated my transaction log and it's down to about 25 megs. The problem is the transaction log is allocated for ar
ound 2.5 gigs and I can't get it to allocated any less so I'm waiting a ton of space on my server. Is there anyway to shrink how much size your allocated for your log file?
Thanks
Check out DBCC SHRINKFILE in BOL.
Andrew J. Kelly SQL MVP
"Chuck" <anonymous@.discussions.microsoft.com> wrote in message
news:9DF0AAF1-1776-4C66-BE74-EE649A3F7CFE@.microsoft.com...
> Hello,
> I have a fairly large database that at one time I used to do a lot of
processing on. Now it's nothing but backup for some old data. I've
truncated my transaction log and it's down to about 25 megs. The problem is
the transaction log is allocated for around 2.5 gigs and I can't get it to
allocated any less so I'm waiting a ton of space on my server. Is there
anyway to shrink how much size your allocated for your log file?
> Thanks
Excesive Space Allocated
Hello,
I have a fairly large database that at one time I used to do a lot of proces
sing on. Now it's nothing but backup for some old data. I've truncated my
transaction log and it's down to about 25 megs. The problem is the transact
ion log is allocated for ar
ound 2.5 gigs and I can't get it to allocated any less so I'm waiting a ton
of space on my server. Is there anyway to shrink how much size your allocat
ed for your log file?
ThanksCheck out DBCC SHRINKFILE in BOL.
Andrew J. Kelly SQL MVP
"Chuck" <anonymous@.discussions.microsoft.com> wrote in message
news:9DF0AAF1-1776-4C66-BE74-EE649A3F7CFE@.microsoft.com...
> Hello,
> I have a fairly large database that at one time I used to do a lot of
processing on. Now it's nothing but backup for some old data. I've
truncated my transaction log and it's down to about 25 megs. The problem is
the transaction log is allocated for around 2.5 gigs and I can't get it to
allocated any less so I'm waiting a ton of space on my server. Is there
anyway to shrink how much size your allocated for your log file?
> Thanks
I have a fairly large database that at one time I used to do a lot of proces
sing on. Now it's nothing but backup for some old data. I've truncated my
transaction log and it's down to about 25 megs. The problem is the transact
ion log is allocated for ar
ound 2.5 gigs and I can't get it to allocated any less so I'm waiting a ton
of space on my server. Is there anyway to shrink how much size your allocat
ed for your log file?
ThanksCheck out DBCC SHRINKFILE in BOL.
Andrew J. Kelly SQL MVP
"Chuck" <anonymous@.discussions.microsoft.com> wrote in message
news:9DF0AAF1-1776-4C66-BE74-EE649A3F7CFE@.microsoft.com...
> Hello,
> I have a fairly large database that at one time I used to do a lot of
processing on. Now it's nothing but backup for some old data. I've
truncated my transaction log and it's down to about 25 megs. The problem is
the transaction log is allocated for around 2.5 gigs and I can't get it to
allocated any less so I'm waiting a ton of space on my server. Is there
anyway to shrink how much size your allocated for your log file?
> Thanks
Sunday, February 26, 2012
Exception from HRESULT: 0x80131904
Hi,
I write a custom component (destination component) that handle the error of my dataflow.
The custom component works fine on design time and runtime by using BIDS.
When I'm using the same package that use my custom component with DTEXEC,
I got the following error :
System.Exception: AcquireConnections : Exception from HRESULT: 0x80131904
at SSISGenerator.SSISErrorHandler.ErrorHandlerDestination.AcquireConnections(
Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnectio
ns(IDTSManagedComponentWrapper90 wrapper, Object transaction)
The error message point out that the problem is in acquireconnections method.
This is the code i'm using in my custom component for the acquireconnections method.
public override void AcquireConnections(object transaction)
{
try
{
if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)
{
ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);
ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;
if (cmado == null)
throw new Exception(String.Format(MSG_ACQUIRECONNECTIONS_ADONET,cm.Name));
this.sqlConnection = cmado.AcquireConnection(null) as SqlConnection;
if (this.sqlConnection == null)
throw new Exception(String.Format(MSG_ACQUIRECONNECTIONS_ADONET, cm.Name));
if (sqlConnection.State != ConnectionState.Open)
this.sqlConnection.Open();
}
}
catch (Exception e)
{
throw new Exception(MSG_ACQUIRECONNECTIONS + e.Message);
}
}
Does someone got an idea ?
Mathieu
Could you try to debug your method while called from DTExec?
Thanks.
|||Hi,
I did some tests and I will explain you the result.
Debug the method with DTExec
I debugged with DTEXEC a solution that contain two project
01 - class library project for the custom component
02 - integration service project for a test package
I did the following tasks for the debug proces
01 - On debug tab, I put the information needed for debugging (start an external program to debug (DTEXEC) and the file for the parameter)
02 - I put the debug dll in GAC
03 - I make sure that I have a breakpoint set on PreExecute
04 - I hit F5 and the error didn't show up.
05 - I took the custom component (dll) and I copy it on the dev server.
06 - I put the debug dll in GAC.
07 - I executed the test package on the dev server with DTEXEC and the error didn't show up.
08 - I remove the debug information and I've compiled the custom component in release mode.
09 - I put the release dll in GAC
10 - I executed the test package on my machine with DTEXEC and the error didn't show up.
11 - I took the custom component (dll) and I copy it on the dev server.
12 - I put the release dll in GAC
13 - I executed the test package on the dev server with DTEXEC and the error didn't show up.
After all these tests, I was suprise since the error message disappears. So, I will do other tests and post the result.
For now, I have one idea for this error message. When I did some tests, I was using the same command line over and over. I'm using a environment variable that indicate the location of my dtsconfig file for the connection string of my connection. I'm not sure if I create the environment variable before openning the commandline. I think it can be one of the reason.
We will see.
Mathieu
Sunday, February 19, 2012
Excel to SQl
Hi All
Thnks for the time
I was trying to figure out how to insert the data that I read from an excel sheet into multiple tables in SQL Database 2005.
// Connection string to the excel file
string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source= D:\\UserData.xls;" +
"Extended Properties=Excel 8.0;";
//Command to read data is
SELECT ID,ProductName,ProductDesc,CategoryName FROM [Products$]
//Connection string for sql 2005 database
string sqlConnectionString = "DataSource=MyServer\\MSSQLEXPRESS;Initial Catalog=TestExcel;Integrated Security=True";
And what is your question ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
Excel spreadsheet to Crystal Report?
does it make sense to try to "convert" an Excel spreadsheet with tons of formulas to a Crystal Report?
I'm having a hell of a time trying to get it working due to the way things are calculated in Crystal like you can't just direct a cloumn of data to divide but a number in a "cell" the running total is killing me.
any ideas?
is this even feasible? should I tell the client to find alternatives?
thanksam I clear in my question?
can anyone comment?
I'm having a hell of a time trying to get it working due to the way things are calculated in Crystal like you can't just direct a cloumn of data to divide but a number in a "cell" the running total is killing me.
any ideas?
is this even feasible? should I tell the client to find alternatives?
thanksam I clear in my question?
can anyone comment?
Friday, February 17, 2012
Excel Pivot Table and Time Dimension
I’m using the OLEDB Provider for Analysis Services 9.0 in Excel 2003 to get to my OLAP cubes in SQL Server 2005 Standard SP1.When I use Time as a Page filter in an Excel Pivot table, the dropdown looks like this (May_2006 is the default period):
-2006
-Qtr2_2006
+May_2006
+Apr_2006
+Jun_2006
+Qtr1_2006
+Qtr3_2006
+Qtr4_2006
+2004
+2005
I checked the field settings in Excel and it is using the data source order option.When I browse the dimension, the Time members are shown in chronological order.I’ve concluded that this behavior is a result of setting the default month in the Time dimension.Anyone know if this is simply the way it is when working with a defaulted Time dimension in Excel?
Moving to SQL Server Analysis Services forum.Wednesday, February 15, 2012
Excel Pivot Table
Hi all,
I have upgrade my AS 2000 cube to As 2005, but there has some thing wrong with the Excel Pivot Table. In the cube I have one Time dimension with three attributes, Year, Month, Day. Then I create a Hierachies with three levels, Year, Month, and Day. Finally I retrieve the data though Excel Pivot Table. I put the Time Hierachies in the page field, and put the Year attribtues in the column filed. But no matter how I filter in the page fields, ex select January 2006 and 2007, the column field will display the whole year of data for 2006 and 2007. I didn't have this problem in AS 2000, anyone know the reason.
Thanks,
Tomas
My first guess is the relationships between the attribute hierarchies may not be correct. Could you describe the attribute relationships explicitly defined in the dimension? Could you also clarify if Month is modeled as January, February, etc. or January 2006, February 2006, ..., January 2007, February 2007, etc.?
Thanks,
Bryan
The attributes relationship as follow:
Date(Usage: key)
|__Calendar Month
ex, 2007-1-1
Calendar Month(Usage: Regular)
|__Calendar Year
ex, January 2007
Calendar Year(Usage: Regular)
ex, 2007
Hierachies
*Calendar Year
**Calendar Month
***Date
Thanks,
Tomas
|||Thomas,
This post was marked as answered. Has the problem been resolved?
Thanks,
Bryan
sorry my mistake to marked as answered. The problem has not been resolved.
Thanks
|||Not really sure what's going on with this. I'd suggest seeing if this is a problem in other browsers. If it is, try replacing the dimension and see if the problem persists. You may also want to open profiler to snag the query being submitted by Excel to see if it is just assembling a weird statement.
B.
|||Here is what Excel generate:
WITH MEMBER [Time].[Year - Quarter - Month - Date].[XL_QZX] AS 'Aggregate ( { [Time].[Year - Quarter - Month - Date].[Quarter].&[2005-01-01T00:00:00] , [Time].[Year - Quarter - Month - Date].[Quarter].&[2004-01-01T00:00:00] , [Time].[Year - Quarter - Month - Date].[Quarter].&[2003-01-01T00:00:00] } )' SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Time].[Year].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [MaxMinSales] WHERE ([Measures].[Store Sales], [Time].[Year - Quarter - Month - Date].[XL_QZX])
But the result is the whole year, instead of Quarter 1.
|||I'm not sure exactly what the problem is, but quarter wasn't in the hierarchy description from above. Take a look at the relationship from month to quarter and then quarter to year. I'm thiking that's where the problem is likely at.
B.
Subscribe to:
Posts (Atom)