Hey guys,
I would like to know how sql archiving is written. What is the best approach
to this such as do this, don't do this stuff? Sample codes will be very
helpful too.
Thanks.
NeilNeil
Look at Vyas's example
CREATE PROC dbo.ArchiveData
(
@.CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete orders from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
INSERT INTO Archive.dbo.Orders
SELECT *
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16,
1)
RETURN -1
END
INSERT INTO Archive.dbo.OrderDetails
SELECT *
FROM dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to
Archive.dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16,
1)
RETURN -1
END
DELETE dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:eaaruzCvEHA.3948@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I would like to know how sql archiving is written. What is the best
approach
> to this such as do this, don't do this stuff? Sample codes will be very
> helpful too.
> Thanks.
> Neil
>sql
Showing posts with label written. Show all posts
Showing posts with label written. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
archiving
Hey guys,
I would like to know how sql archiving is written. What is the best approach
to this such as do this, don't do this stuff? Sample codes will be very
helpful too.
Thanks.
Neil
Neil
Look at Vyas's example
CREATE PROC dbo.ArchiveData
(
@.CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete orders from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
INSERT INTO Archive.dbo.Orders
SELECT *
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16,
1)
RETURN -1
END
INSERT INTO Archive.dbo.OrderDetails
SELECT *
FROM dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to
Archive.dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16,
1)
RETURN -1
END
DELETE dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:eaaruzCvEHA.3948@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I would like to know how sql archiving is written. What is the best
approach
> to this such as do this, don't do this stuff? Sample codes will be very
> helpful too.
> Thanks.
> Neil
>
I would like to know how sql archiving is written. What is the best approach
to this such as do this, don't do this stuff? Sample codes will be very
helpful too.
Thanks.
Neil
Neil
Look at Vyas's example
CREATE PROC dbo.ArchiveData
(
@.CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete orders from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
INSERT INTO Archive.dbo.Orders
SELECT *
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16,
1)
RETURN -1
END
INSERT INTO Archive.dbo.OrderDetails
SELECT *
FROM dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to
Archive.dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16,
1)
RETURN -1
END
DELETE dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:eaaruzCvEHA.3948@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I would like to know how sql archiving is written. What is the best
approach
> to this such as do this, don't do this stuff? Sample codes will be very
> helpful too.
> Thanks.
> Neil
>
archiving
Hey guys,
I would like to know how sql archiving is written. What is the best approach
to this such as do this, don't do this stuff? Sample codes will be very
helpful too.
Thanks.
NeilNeil
Look at Vyas's example
CREATE PROC dbo.ArchiveData
(
@.CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete orders from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
INSERT INTO Archive.dbo.Orders
SELECT *
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16,
1)
RETURN -1
END
INSERT INTO Archive.dbo.OrderDetails
SELECT *
FROM dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to
Archive.dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16,
1)
RETURN -1
END
DELETE dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:eaaruzCvEHA.3948@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I would like to know how sql archiving is written. What is the best
approach
> to this such as do this, don't do this stuff? Sample codes will be very
> helpful too.
> Thanks.
> Neil
>
I would like to know how sql archiving is written. What is the best approach
to this such as do this, don't do this stuff? Sample codes will be very
helpful too.
Thanks.
NeilNeil
Look at Vyas's example
CREATE PROC dbo.ArchiveData
(
@.CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete orders from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
INSERT INTO Archive.dbo.Orders
SELECT *
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16,
1)
RETURN -1
END
INSERT INTO Archive.dbo.OrderDetails
SELECT *
FROM dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to
Archive.dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @.CutOffDate
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16,
1)
RETURN -1
END
DELETE dbo.Orders
WHERE OrderDate < @.CutOffDate
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:eaaruzCvEHA.3948@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I would like to know how sql archiving is written. What is the best
approach
> to this such as do this, don't do this stuff? Sample codes will be very
> helpful too.
> Thanks.
> Neil
>
Wednesday, March 7, 2012
Application Roles in SQL 2005
We have an an application that was written using OLE DB (ADO) against a SQL 2000 Server that uses an Application role to give rights to the database objects. It connects, calls sp_setapprole and goes on. If the database needs to LOCK a record, it is creating a new ADO Connection and instantiating the Approle again. This model has been working fine up til now.
Now we are installing a SQL 2005 server for the latest version of the product we are working on and are running into an error. The error is
Error: 18059, Severity: 20, State: 1.
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.
It's happening when the second ADO Connection for locking a record is being created and the sp_setapprole is being executed.
One of my questions is what is the problem with executing the approle on a different connection? Our code has not changed, so obviously SQL 2005 is doing something different. The other is What can we do to correct this?
Is the resource pooling different? We had problems in the beginning with approles and figured out through research that we needed to add OLE DB Services=-2 to the connection string to turn off resource pooling.
Is there an extra step to using Approles in SQL 2005?
Any help would be greatly appreciated as we need to resolve this ASAP.
Thanks,
David
SQL Server 2005 supports a richer impersonation model, and we made some changes to prevent potential for undesired escalation of privileges on both the existing and new impersonation mechanisms, including approles.
From your description I can guess that you are using a connection pool, and apparently the pool is misusing an impersonated context (approle). Does it still fail if you disable the connection pool?
If this is the case, one solution for using approles and connection pool would be to use the new @.fCreateCookie and @.cookie parameters in sp_setapprole along with sp_unsetapprole (http://msdn2.microsoft.com/en-us/library/ms365415.aspx). This allows the client code to obtain a server-generated cookie that can be used to revert to the original context before resetting the connection.
Unfortunately I haven’t experienced any behavior like the one you described; at this point I cannot really tell if the behavior you are experiencing is a problem on the way the client code is calling SQL Server, if this is a scenario where we decided to change the behavior of SQL Server to prevent an undesired escalation of privileges or a real bug in SQL Server 2005.
Please, let us know if the workaround I suggested work, if not I would like to understand more about the scenario so I can try to reproduce this behavior on my test environment.
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
|||We don't need to revert to the original context, so It's supposed to be disabling the resource pooling by adding "OLE DB Services=-2" on the connection string of the ADOConnection. We found the problem with connection pooling coding against SQL 2000.
Is that still effective in a SQL 2005 environment? Has the setting changed? It seems like something isn't set right since this worked in SQL 2000 and doesn't in SQL 2005.
Thanks,
David
|||To clarify this situation more....
We aren't using .NET and the SqlClient for this app.
We are running a Win32 application, connecting to the SQL Server using ADO (MDAC 2.7 or 2.8), holding this connection open until the application closes at which point we close the connection. We are turning on ROW Level locking in SQL 2000 (not sure if this has been done or not on the SQL 2005 server) and using a separate connection with "locking hints" on the tables when a lock call is made to force a model we have to use for backwards compatability. (i.e.
select * from authors as a WITH (NOLOCK) { normal get }
select * from authors as a with (UPDLOCK) where identitycol = 1 { get with a lock for updating }
)
I wasn't sure how SQL Server 2005 would handle connectivity from the SQL 2000 client with sp3.
|||Ok. Found the problem.
In certain circumstances, the connection string was being set with the OLE DB Services=-2 like it was supposed to be. Therefore the Pooling was still on and causing the problem.
Thanks for the assistance!
Thursday, February 9, 2012
Anything wrong with the connection?
Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
Ivan
On Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
Ivan
On Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA
Anything wrong with the connection?
Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Use
r
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL serve
r
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Use
r
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL serve
r
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA
Anything wrong with the connection?
Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
--
Rudi Bruchez
MCDBA
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
--
Rudi Bruchez
MCDBA
Anyone written an end user guide to using the Reportbuilder?
For those using the ReportBuilder, has anyone put together any sort of end-user guides to getting started with the tool? I'm not referring to articles and docs written from the perspective of introducing the tool and feature set to developers and DBAs but rather something we could all then give our end users when they first start using the tool to build and run reports.
I realize there's lots (and lots) of online help within the tool, but we all know that some users never read that. Again, I'm thinking of something ranging from a 1-2 page quick intro to the use and features, to perhaps even a several page guide.
Even if someone's written one for their company and so it's branded as such, if you're open to sharing it, I won't mind rebranding and modifying it (and happy to share that result then with others).
Indeed, is there perhaps something from MS that I've not thought of?
Raising this again to see if there are any takers. :-)|||I am also looking for the same thing- let me know if you have any luck.Anyone written an end user guide to using the Reportbuilder?
For those using the ReportBuilder, has anyone put together any sort of end-user guides to getting started with the tool? I'm not referring to articles and docs written from the perspective of introducing the tool and feature set to developers and DBAs but rather something we could all then give our end users when they first start using the tool to build and run reports.
I realize there's lots (and lots) of online help within the tool, but we all know that some users never read that. Again, I'm thinking of something ranging from a 1-2 page quick intro to the use and features, to perhaps even a several page guide.
Even if someone's written one for their company and so it's branded as such, if you're open to sharing it, I won't mind rebranding and modifying it (and happy to share that result then with others).
Indeed, is there perhaps something from MS that I've not thought of?
Raising this again to see if there are any takers. :-)|||I am also looking for the same thing- let me know if you have any luck.
Subscribe to:
Posts (Atom)