SQL server questions: March 2012

Thursday, March 29, 2012

about activeX script error in ssis package in SQL server 2005

when i run activex Script it's shows this error

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438

Moving from .NET Framework Data Access and Storage...|||So is there a solution to this?
I've had a similar error when using the "scripting.filesystemobject" from within a script task in ssis.

about activeX script error in ssis package in SQL server 2005

when i run activex Script it's shows this error

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438

Moving from .NET Framework Data Access and Storage...|||So is there a solution to this?
I've had a similar error when using the "scripting.filesystemobject" from within a script task in ssis.

about accessing subReport's item

Hi all,
Is there anyone know how to access subReport's item? For example, can i use:
Reports!subReport.controls.textbox1.value in main report to access
subreport's textbox value? I have tried that, but failed. :-(
Thanks,
LisaHello Lisa,
Sadly no, I dont think you can do that.
The only property that I recall when looking at the IL that is exposed on a
ReportItem appears to be the Value property and that's I think is typed as a
String.
So on a report itself you can have a TextBox say called "TextBox1" and that
might be bound to a value from the database, and then you could bind another
TextBox to the first one with this syntax
=ReportItems!TextBox1.Value.
This kind of trick we discuss on p378 and 379 of our book where we explain
that it is useful for when you want to get image content from a DataSet into
a Report Header or Footer.
Peter Blackburn
Author: Hitchhiker's Guide to SQL Server 2000 Reporting Services
www.sqlreportingservices.net
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:8BDED34A-EC44-41B0-AC89-4763086BD414@.microsoft.com...
> Hi all,
> Is there anyone know how to access subReport's item? For example, can i
> use:
> Reports!subReport.controls.textbox1.value in main report to access
> subreport's textbox value? I have tried that, but failed. :-(
> Thanks,
> Lisa

about accessing SQL Server2005 database file from a remote computer

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

Hi,

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

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

Thanks.

|||

Hi,

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

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

Eralper

sql

About accessing data from sqlserver

Hello sir,
I have installed .net1.1version on windows2003 operating system.I have also installed sqlserver2000 on the same system.
My problem is that when i am trying to get data from sqlserver from asp.net program i am getting error as
'access denied to user NT AUTHORITY/NETWORK USER'.
I request you to kindly suggest me with an appropriate solution.
Thanking you.
Please email meon:-aanandkumar786@.yahoo.comeither the account that asp.net uses to run pages needs to be given access to the database, or you need to create your sql connection string with a specific sql server account to allow for the authentication.

about a WHERE

Hi, I think this is an easy stuff but not for me.. I have an application
running on a production server.
In my DB I have a Sales Table like this
SaleDate Product Price$
2005-02-18 00:00:00.000 1 500
2005-02-18 00:00:00.000 1 100
2005-02-18 00:00:00.000 3 200
Using SP I'm getting a total by Date by product (e.g For SaleDate =
2005-02-18 Product 1 = 600, Product 3 = 200)
Now also I need to Store the times like this
SaleDate Product Price$
2005-02-18 09:37:39.000 1 500
2005-02-18 09:30:09.000 1 100
2005-02-18 14:20:10.000 3 200
How should I modify my SP to get the same..I tried this:
Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
but it does nothing, the SP return a total for each row
thks.If you want daily totals per product for the given data, try:
select
convert (datetime, convert (char (8), SaleDate, 112), 112) as SaleDate
, Product
, sum (Price) as Total
from
Sales
group by
convert (datetime, convert (char (8), SaleDate, 112), 112)
, Product
order by
SaleDate
, Product
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:7D87C046-13C6-4DE7-8932-4BC5BE369155@.microsoft.com...
Hi, I think this is an easy stuff but not for me.. I have an application
running on a production server.
In my DB I have a Sales Table like this
SaleDate Product Price$
2005-02-18 00:00:00.000 1 500
2005-02-18 00:00:00.000 1 100
2005-02-18 00:00:00.000 3 200
Using SP I'm getting a total by Date by product (e.g For SaleDate =
2005-02-18 Product 1 = 600, Product 3 = 200)
Now also I need to Store the times like this
SaleDate Product Price$
2005-02-18 09:37:39.000 1 500
2005-02-18 09:30:09.000 1 100
2005-02-18 14:20:10.000 3 200
How should I modify my SP to get the same..I tried this:
Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
but it does nothing, the SP return a total for each row
thks.|||This should answer your question..
given that the SaleDate is a datetime (you have seconds in your second
snippet)
DECLARE @.Date datetime
SET @.Date = '20050218' -- no seconds in here
... WHERE SaleDate>=@.Date AND SaleDate<@.Date+1
-- note the >= on LHS and the < on RHS to prevent overlaps
If possible, dont cast the column in your table to compare it as that
precludes the optimiser from using an index s

(I hate it when the
optimiser does implicit casts on the column rather than the variable).
Mr Tea
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:7D87C046-13C6-4DE7-8932-4BC5BE369155@.microsoft.com...
> Hi, I think this is an easy stuff but not for me.. I have an application
> running on a production server.
> In my DB I have a Sales Table like this
> SaleDate Product Price$
> 2005-02-18 00:00:00.000 1 500
> 2005-02-18 00:00:00.000 1 100
> 2005-02-18 00:00:00.000 3 200
> Using SP I'm getting a total by Date by product (e.g For SaleDate =
> 2005-02-18 Product 1 = 600, Product 3 = 200)
> Now also I need to Store the times like this
> SaleDate Product Price$
> 2005-02-18 09:37:39.000 1 500
> 2005-02-18 09:30:09.000 1 100
> 2005-02-18 14:20:10.000 3 200
> How should I modify my SP to get the same..I tried this:
> Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
> CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
> but it does nothing, the SP return a total for each row
> thks.
>
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
>>From sys.sysdatabases
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.
NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegr oups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>
|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End
|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegr oups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable')
=
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/U...2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable'
)
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTab
le'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTabl
e')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>sql

About a RS version and previous conditions of use

I own a Windows Small Bussiness 2003 license which includes SQL server 2000
Standard Edition and additionally came with a version of Reporting Services.
I want to learn and use it (Reporting Services) as a beginner but when I
try to install it a message appears indicating that I need to install or
configure previously two products:
a) Visual Studio .Net 2003
b) IIS 5.0
Do I need both of 'em just to begin doing simple reports?
I supposed a simple use like I could obtain through Crystal reports 7.0 or
so on.
Please, help me.
Probably next year we will migrate to a new version of Microsoft SBS
Is it worth to do efforts with the versions I own nowadays or not?
Thanks alot in advance.
--
sanpetusRS 2000 report designer require some copy of VS 2003 to be installed. In the
past VB.net 2003 was the cheapest way to do this (about $100). I don't know
now. Note that the VB 2005 will not work for this.
In RS 2005 it comes with a version of VS 2005 so no additional purchase is
necessary.
RS is a asp.net application and as such it needs IIS. IIS comes with all
servers. It might need to configured though.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sanpetus" <sanpetus@.discussions.microsoft.com> wrote in message
news:14FE57B4-A059-4B0F-8539-C9D64CBAA6B6@.microsoft.com...
>I own a Windows Small Bussiness 2003 license which includes SQL server 2000
> Standard Edition and additionally came with a version of Reporting
> Services.
> I want to learn and use it (Reporting Services) as a beginner but when I
> try to install it a message appears indicating that I need to install or
> configure previously two products:
> a) Visual Studio .Net 2003
> b) IIS 5.0
> Do I need both of 'em just to begin doing simple reports?
> I supposed a simple use like I could obtain through Crystal reports 7.0 or
> so on.
> Please, help me.
> Probably next year we will migrate to a new version of Microsoft SBS
> Is it worth to do efforts with the versions I own nowadays or not?
> Thanks alot in advance.
> --
> sanpetus

About a Free MSDE manager

Hello!!
Can you advice me some free MSDE tools (like database manager, query
interface...) that can be found over web?
Thanks in advance for your help!!
Ambros Moreno
From Almeria (Spain)
http://www.aspfaq.com/2442
http://www.aspfaq.com/
(Reverse address to reply.)
"Ambros" <ambros@.sasao.com> wrote in message
news:Obexjnf1EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hello!!
> Can you advice me some free MSDE tools (like database manager, query
> interface...) that can be found over web?
> Thanks in advance for your help!!
> Ambros Moreno
> From Almeria (Spain)
>
|||Thanks for the link Aaron!! is really usefull.!!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribi en el mensaje
news:eOwVYai1EHA.2156@.TK2MSFTNGP10.phx.gbl...
> http://www.aspfaq.com/2442
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Ambros" <ambros@.sasao.com> wrote in message
> news:Obexjnf1EHA.2804@.TK2MSFTNGP15.phx.gbl...
>

About a DataType

What kind of Datatype can I use in my DB to store info like a letter or Rich
Text, including the formats?
thks
--Depending on the expected length including RTF codes, you can either use any
of
the character types (Char, VarChar, NChar, NVarChar, Text or NText).
Thomas
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:9A7D59EE-EF4C-4F01-B661-9A6C5F0413D8@.microsoft.com...
> What kind of Datatype can I use in my DB to store info like a letter or Ri
ch
> Text, including the formats?
> thks
> --
>|||If the documents size can exceed 8k, Use Image type. If all docs are
guaranteed to be less than 8k you cann use Binary...
"Kenny M." wrote:

> What kind of Datatype can I use in my DB to store info like a letter or Ri
ch
> Text, including the formats?
> thks
> --
>

about 8KB limit

In SQL2005 with the option ROW_OVERFLOW_DATA the restriction of 8KB by row relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. In this case SQL Server use as best the page size, however I wonder what happen when this option is turned off.

For example, If a row size is of 3 KB and I have ROW_OVERFLOW_DATA OFF how SQL Server store my rows? there are about 2 KB of wasted space by page?

There is no such thing called ROW_OVERFLOW_DATA option. You cannot turn it on or off. It is based on the column type. If you have variable length columns, sql server allows you to store rows larger than 8k by pushing variable length column values off-row.

If your row size is 3KB fixed size, you will waste 2KB in each page. There is no way to work around and re-use those 2KB space.

Thanks

Sherry

about 8KB limit

For example, If a row size is of 3 KB and I have ROW_OVERFLOW_DATA OFF how SQL Server store my rows? there are about 2 KB of wasted space by page?

If your row size is 3KB fixed size, you will waste 2KB in each page. There is no way to work around and re-use those 2KB space.

Thanks

Sherry

sql

about 32 bit application and odbc to access 64 bit SQL Server 2005

Hello
Our application currently is running on IIS machine with Windows 2003 32 bit
OS installed and database server machine with Windows 2000 32 bit OS and SQL
server 2000 installed.
We are thinking to upgrade the database server to Windows 2003 64 bit OS and
SQL server 2005 64 bit.
The IIS machine is still on 32 bit OS and application, We are wondering if
there is any problem for 32 bit application and ODBC to access the 64 bit
database?
Thanks in advance
Lionel
No, there is no problem. I least I have seen dozens of similar applications
accesing 64-bit databases.
Most of the connectivity issues I have seen is connecting SQL Server 64-bit
to Oracle but there is always a solution.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"lionel" wrote:

> Hello
> Our application currently is running on IIS machine with Windows 2003 32 bit
> OS installed and database server machine with Windows 2000 32 bit OS and SQL
> server 2000 installed.
> We are thinking to upgrade the database server to Windows 2003 64 bit OS and
> SQL server 2005 64 bit.
> The IIS machine is still on 32 bit OS and application, We are wondering if
> there is any problem for 32 bit application and ODBC to access the 64 bit
> database?
> Thanks in advance
> Lionel
|||Lionel,
Here is a nice article on migrating to 64 bit SQL Server and the drivers it
takes to connect.
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1241693,00.html
The only issue when using a 64 bit OS that I have noticed is on the Jet
(Access) connector which doesnt exist on 64 bit.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:EF862D05-83A0-4F45-A0DE-D12F696E7EE8@.microsoft.com...[vbcol=seagreen]
> No, there is no problem. I least I have seen dozens of similar
> applications
> accesing 64-bit databases.
> Most of the connectivity issues I have seen is connecting SQL Server
> 64-bit
> to Oracle but there is always a solution.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "lionel" wrote:

Tuesday, March 27, 2012

about 32 bit application and odbc to access 64 bit SQL Server 2005

Hello
Our application currently is running on IIS machine with Windows 2003 32 bit
OS installed and database server machine with Windows 2000 32 bit OS and SQL
server 2000 installed.
We are thinking to upgrade the database server to Windows 2003 64 bit OS and
SQL server 2005 64 bit.
The IIS machine is still on 32 bit OS and application, We are wondering if
there is any problem for 32 bit application and ODBC to access the 64 bit
database?
Thanks in advance
LionelNo, there is no problem. I least I have seen dozens of similar applications
accesing 64-bit databases.
Most of the connectivity issues I have seen is connecting SQL Server 64-bit
to Oracle but there is always a solution.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"lionel" wrote:

> Hello
> Our application currently is running on IIS machine with Windows 2003 32 b
it
> OS installed and database server machine with Windows 2000 32 bit OS and S
QL
> server 2000 installed.
> We are thinking to upgrade the database server to Windows 2003 64 bit OS a
nd
> SQL server 2005 64 bit.
> The IIS machine is still on 32 bit OS and application, We are wondering if
> there is any problem for 32 bit application and ODBC to access the 64 bit
> database?
> Thanks in advance
> Lionel|||Lionel,
Here is a nice article on migrating to 64 bit SQL Server and the drivers it
takes to connect.
http://searchsqlserver.techtarget.c...1241693,00.html
The only issue when using a 64 bit OS that I have noticed is on the Jet
(Access) connector which doesnt exist on 64 bit.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:EF862D05-83A0-4F45-A0DE-D12F696E7EE8@.microsoft.com...[vbcol=seagreen]
> No, there is no problem. I least I have seen dozens of similar
> applications
> accesing 64-bit databases.
> Most of the connectivity issues I have seen is connecting SQL Server
> 64-bit
> to Oracle but there is always a solution.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "lionel" wrote:
>

about 32 bit application and odbc to access 64 bit SQL Server 2005

Hello
Our application currently is running on IIS machine with Windows 2003 32 bit
OS installed and database server machine with Windows 2000 32 bit OS and SQL
server 2000 installed.
We are thinking to upgrade the database server to Windows 2003 64 bit OS and
SQL server 2005 64 bit.
The IIS machine is still on 32 bit OS and application, We are wondering if
there is any problem for 32 bit application and ODBC to access the 64 bit
database?
Thanks in advance
LionelNo, there is no problem. I least I have seen dozens of similar applications
accesing 64-bit databases.
Most of the connectivity issues I have seen is connecting SQL Server 64-bit
to Oracle but there is always a solution.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"lionel" wrote:
> Hello
> Our application currently is running on IIS machine with Windows 2003 32 bit
> OS installed and database server machine with Windows 2000 32 bit OS and SQL
> server 2000 installed.
> We are thinking to upgrade the database server to Windows 2003 64 bit OS and
> SQL server 2005 64 bit.
> The IIS machine is still on 32 bit OS and application, We are wondering if
> there is any problem for 32 bit application and ODBC to access the 64 bit
> database?
> Thanks in advance
> Lionel|||Lionel,
Here is a nice article on migrating to 64 bit SQL Server and the drivers it
takes to connect.
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1241693,00.html
The only issue when using a 64 bit OS that I have noticed is on the Jet
(Access) connector which doesnt exist on 64 bit.
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:EF862D05-83A0-4F45-A0DE-D12F696E7EE8@.microsoft.com...
> No, there is no problem. I least I have seen dozens of similar
> applications
> accesing 64-bit databases.
> Most of the connectivity issues I have seen is connecting SQL Server
> 64-bit
> to Oracle but there is always a solution.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "lionel" wrote:
>> Hello
>> Our application currently is running on IIS machine with Windows 2003 32
>> bit
>> OS installed and database server machine with Windows 2000 32 bit OS and
>> SQL
>> server 2000 installed.
>> We are thinking to upgrade the database server to Windows 2003 64 bit OS
>> and
>> SQL server 2005 64 bit.
>> The IIS machine is still on 32 bit OS and application, We are wondering
>> if
>> there is any problem for 32 bit application and ODBC to access the 64 bit
>> database?
>> Thanks in advance
>> Lionel

About @@ERROR in SQL 2005 online book

Here

http://msdn2.microsoft.com/en-us/library/ms190193.aspx

it is explained that @.@.ERROR will be cleared and reset.

But here:

http://msdn2.microsoft.com/en-us/library/ms190248.aspx

http://msdn2.microsoft.com/en-us/library/ms187009.aspx

we still see:

IF (@.@.ERROR <> 0)

SET @.ErrorSave = @.@.ERROR

Chester

Is there a question here?

I assume you are asking if @.@.Error is reset during the IF statement. If you read the first link, it says NO.

http://msdn2.microsoft.com/en-us/library/ms190193.aspx

@.@.Error is reset by the next TSQL command. IF is a conditional statement and does not reset the flag.|||

Hi Chester,

The second example is indeed incorrect as @.@.error will be reset to 0 by the IF statement. The local @.ErrorSave should be set after each statement and them compared:

declare @.a int, @.ErrorSave int;

set @.a = (1 / 0); -- divide by zero

set @.ErrorSave = @.@.ERROR;

if (@.ErrorSave <> 0)
begin
select @.ErrorSave AS [ErrorNumber]
end

Cheers,
Rob

|||

Hi Tom,

That's sort of incorrect - the IF will return TRUE when evaluating the statement "IF (@.@.EROR <> 0)", however @.@.ERROR is then reset by the successful completion of the evaluation:

declare @.a int, @.ErrorSave int;

set @.a = (1 / 0); -- divide by zero

if (@.@.error <> 0)
begin
select @.@.error AS [ErrorNumber] -- will return 0
end

Cheers,
Rob

|||

Here

http://msdn2.microsoft.com/en-us/library/ms190193.aspx

It says exactly:

Conditional statements, such as the IF statement, reset @.@.ERROR. If you reference @.@.ERROR in an IF statement, references to @.@.ERROR in the IF or ELSE blocks will not retrieve the @.@.ERROR information. In the following example, @.@.ERROR is reset by IF and does not return the error number when referenced in the PRINT statement.

|||

Thanks, Robert.

Actually I found this problem in one of our partner's application that was wriiten by a local Robert.

When I checked the online book, it was misleading at first .

Chester

|||Yep, you are right, my mistake.

@.@.Error is reset by the IF. The best thing to do is: SET @.errorcode = @.@.ERROR right after the command.

The @.@.Error traps so little errors, it is almost worthless. Most of the time the error in the command just terminates the stored proc and never gets to the error trap anyway, unless you use Try/Catch.

About @@ERROR in SQL 2005 online book

Here

http://msdn2.microsoft.com/en-us/library/ms190193.aspx

it is explained that @.@.ERROR will be cleared and reset.

But here:

http://msdn2.microsoft.com/en-us/library/ms190248.aspx

http://msdn2.microsoft.com/en-us/library/ms187009.aspx

we still see:

IF (@.@.ERROR <> 0)

SET @.ErrorSave = @.@.ERROR

Chester

Is there a question here?

I assume you are asking if @.@.Error is reset during the IF statement. If you read the first link, it says NO.

http://msdn2.microsoft.com/en-us/library/ms190193.aspx

@.@.Error is reset by the next TSQL command. IF is a conditional statement and does not reset the flag.|||

Hi Chester,

The second example is indeed incorrect as @.@.error will be reset to 0 by the IF statement. The local @.ErrorSave should be set after each statement and them compared:

declare @.a int, @.ErrorSave int;

set @.a = (1 / 0); -- divide by zero

set @.ErrorSave = @.@.ERROR;

if (@.ErrorSave <> 0)
begin
select @.ErrorSave AS [ErrorNumber]
end

Cheers,
Rob

|||

Hi Tom,

That's sort of incorrect - the IF will return TRUE when evaluating the statement "IF (@.@.EROR <> 0)", however @.@.ERROR is then reset by the successful completion of the evaluation:

declare @.a int, @.ErrorSave int;

set @.a = (1 / 0); -- divide by zero

if (@.@.error <> 0)
begin
select @.@.error AS [ErrorNumber] -- will return 0
end

Cheers,
Rob

|||

Here

http://msdn2.microsoft.com/en-us/library/ms190193.aspx

It says exactly:

|||

Thanks, Robert.

Actually I found this problem in one of our partner's application that was wriiten by a local Robert.

When I checked the online book, it was misleading at first .

Chester

|||Yep, you are right, my mistake.

@.@.Error is reset by the IF. The best thing to do is: SET @.errorcode = @.@.ERROR right after the command.

About @@ERROR

Hi,
Can I use @.@.ERROR global variable to check the error returned by SQL DDL
commands.Are you saying that it doesn't work? Can you elaborate on your question? Som
e error will terminate
the batch. I suggest you check the two articles on error handling here: http://www
.sommarskog.se/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
news:4CC53AC2-1747-4C8F-851B-2D057A2487E7@.microsoft.com...
> Hi,
> Can I use @.@.ERROR global variable to check the error returned by SQL DDL
> commands.
>|||IN addition to Tibor's comments, and you will find included in Erlands
articles.
Yes you can ( and should) use @.@.error to check DDL... You might also check
for existence of the object or column, etc..
The problem is that some errors cause the entire batch, or maybe just the
statement to abort, in which case, the rest of your code wouldn't run or
@.@.error might not be set appropriately..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
news:4CC53AC2-1747-4C8F-851B-2D057A2487E7@.microsoft.com...
> Hi,
> Can I use @.@.ERROR global variable to check the error returned by SQL DDL
> commands.
>

About @@ERROR

Hi,
Can I use @.@.ERROR global variable to check the error returned by SQL DDL
commands.
Are you saying that it doesn't work? Can you elaborate on your question? Some error will terminate
the batch. I suggest you check the two articles on error handling here: http://www.sommarskog.se/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
news:4CC53AC2-1747-4C8F-851B-2D057A2487E7@.microsoft.com...
> Hi,
> Can I use @.@.ERROR global variable to check the error returned by SQL DDL
> commands.
>
|||IN addition to Tibor's comments, and you will find included in Erlands
articles.
Yes you can ( and should) use @.@.error to check DDL... You might also check
for existence of the object or column, etc..
The problem is that some errors cause the entire batch, or maybe just the
statement to abort, in which case, the rest of your code wouldn't run or
@.@.error might not be set appropriately..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
news:4CC53AC2-1747-4C8F-851B-2D057A2487E7@.microsoft.com...
> Hi,
> Can I use @.@.ERROR global variable to check the error returned by SQL DDL
> commands.
>

About @@ERROR

Hi,
Can I use @.@.ERROR global variable to check the error returned by SQL DDL
commands.Are you saying that it doesn't work? Can you elaborate on your question? Some error will terminate
the batch. I suggest you check the two articles on error handling here: http://www.sommarskog.se/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
news:4CC53AC2-1747-4C8F-851B-2D057A2487E7@.microsoft.com...
> Hi,
> Can I use @.@.ERROR global variable to check the error returned by SQL DDL
> commands.
>|||IN addition to Tibor's comments, and you will find included in Erlands
articles.
Yes you can ( and should) use @.@.error to check DDL... You might also check
for existence of the object or column, etc..
The problem is that some errors cause the entire batch, or maybe just the
statement to abort, in which case, the rest of your code wouldn't run or
@.@.error might not be set appropriately..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shri.DBA" <ShriDBA@.discussions.microsoft.com> wrote in message
news:4CC53AC2-1747-4C8F-851B-2D057A2487E7@.microsoft.com...
> Hi,
> Can I use @.@.ERROR global variable to check the error returned by SQL DDL
> commands.
>

about "trailing Space" in the record

Hi, there;
We know that: Both select * from mytable where column1="data" and select * from mytable where column1="data " give us same result. (Please note the spaces in second query) This means the trailing space doesn't affect the query result.

How can I make SQL to return different result?

Thanks.

That is the result of the ANSI standard.

One way that you could use to determine if the values, including trailing blanks, are different, is to use the datalength() functions. Something like this:

Code Snippet

DECLARE
@.MyVar1 varchar(20),
@.MyVar2 varchar(20)

SELECT
@.MyVar1 = 'data',
@.MyVar2 = 'data '

IF datalength( @.MyVar1 ) = datalength( @.MyVar2 )
PRINT 'Values are the same'
ELSE
PRINT 'Values are NOT the same'

|||Thanks Arnie.

My case is a bit different. I need to run a sql statement (DELETE FROM MYTABLE WHERE key='KEYVALUE') from an application. The data in the table is imported from raining data. Some data has trailing space. Because of the ANSI standard, 'KEYVALUE ' will be deleted if I run that statement which is not I want.

I just wonder if there is a switch to we can turn it on/off to make it different.|||

Perhaps this will work for you:

WHERE ( KeyValue = 'KeyValue'

AND datalength( KeyValue ) = datalength( 'KeyValue' )
)

|||Thanks. That will do.

About "MONTH" datediff

Hi,
I'm trying to make a "MONTH" DATEDIFF result.
If the month of GETDATE() is January then I want the result is the December
of last year.
Like GETDATE() = 200601 THEN GETDATE()-1(Month) = 200512
How can I do this?
Thanks for any advice!
Angideclare @.dt datetime
set @.dt = getdate()
SELECT DATEADD(month, -1, @.dt)
William Stacey [MVP]
"Angi" <enchiw@.msn.com> wrote in message
news:e6ueCBgBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to make a "MONTH" DATEDIFF result.
> If the month of GETDATE() is January then I want the result is the
> December of last year.
> Like GETDATE() = 200601 THEN GETDATE()-1(Month) = 200512
> How can I do this?
> Thanks for any advice!
> Angi
>|||Wow, Thanks!
Angi
"William Stacey [MVP]" <william.stacey@.gmail.com> glsD:eLgUTGgBGHA.4032@.TK2MSF
TNGP10.phx.gbl...
> declare @.dt datetime
> set @.dt = getdate()
> SELECT DATEADD(month, -1, @.dt)
>
> --
> William Stacey [MVP]
> "Angi" <enchiw@.msn.com> wrote in message
> news:e6ueCBgBGHA.736@.TK2MSFTNGP10.phx.gbl...
>sql

About "MONTH" datediff

Hi,
I'm trying to make a "MONTH" DATEDIFF result.
If the month of GETDATE() is January then I want the result is the December
of last year.
Like GETDATE() = 200601 THEN GETDATE()-1(Month) = 200512
How can I do this?
Thanks for any advice!
Angi
declare @.dt datetime
set @.dt = getdate()
SELECT DATEADD(month, -1, @.dt)
William Stacey [MVP]
"Angi" <enchiw@.msn.com> wrote in message
news:e6ueCBgBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to make a "MONTH" DATEDIFF result.
> If the month of GETDATE() is January then I want the result is the
> December of last year.
> Like GETDATE() = 200601 THEN GETDATE()-1(Month) = 200512
> How can I do this?
> Thanks for any advice!
> Angi
>
|||Wow, Thanks!
Angi
"William Stacey [MVP]" <william.stacey@.gmail.com> glsD:eLgUTGgBGHA.4032@.TK2MSFTNGP10.phx.g bl...
> declare @.dt datetime
> set @.dt = getdate()
> SELECT DATEADD(month, -1, @.dt)
>
> --
> William Stacey [MVP]
> "Angi" <enchiw@.msn.com> wrote in message
> news:e6ueCBgBGHA.736@.TK2MSFTNGP10.phx.gbl...
>

Aborting CALL to stored procedure

Hello

I am calling a stored procedure in a MSDE/SQLServer DB form within my
Visual C++ 6.0 program along the lines
CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
With
DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
It all works sweet as, but it can take a while and I want to let the
user abort it.
Everything I've tried ends in tears.Hi

You can issue a KILL command on the SQL Server which will terminate the
process. To do this you are going to need a separate thread. More
information in books online.

John

"Mike Brown" <browna@.beer.com> wrote in message
news:ea197978.0406302059.3f4b8524@.posting.google.c om...
> Hello
> I am calling a stored procedure in a MSDE/SQLServer DB form within my
> Visual C++ 6.0 program along the lines
> CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> With
> DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> It all works sweet as, but it can take a while and I want to let the
> user abort it.
> Everything I've tried ends in tears.|||I have the command running in a separate thread.
I dont want to kill the server, just the CALL. I have tried killing
the thread and using .Abort(), and most other things I can think of,
but everything results in my program crashing.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<AKPEc.694$t8.6278387@.news-text.cableinet.net>...
> Hi
> You can issue a KILL command on the SQL Server which will terminate the
> process. To do this you are going to need a separate thread. More
> information in books online.
> John
> "Mike Brown" <browna@.beer.com> wrote in message
> news:ea197978.0406302059.3f4b8524@.posting.google.c om...
> > Hello
> > I am calling a stored procedure in a MSDE/SQLServer DB form within my
> > Visual C++ 6.0 program along the lines
> > CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> > With
> > DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> > It all works sweet as, but it can take a while and I want to let the
> > user abort it.
> > Everything I've tried ends in tears.|||Hi

I am not sure what you mean by killing the server. Look up the KILL command
in books online.
Killing your thread should not result in the program crashing, but may leave
an orphaned process on the SQL server.

John

"Mike Brown" <browna@.beer.com> wrote in message
news:ea197978.0407011050.4a44f3c5@.posting.google.c om...
> I have the command running in a separate thread.
> I dont want to kill the server, just the CALL. I have tried killing
> the thread and using .Abort(), and most other things I can think of,
> but everything results in my program crashing.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<AKPEc.694$t8.6278387@.news-text.cableinet.net>...
> > Hi
> > You can issue a KILL command on the SQL Server which will terminate the
> > process. To do this you are going to need a separate thread. More
> > information in books online.
> > John
> > "Mike Brown" <browna@.beer.com> wrote in message
> > news:ea197978.0406302059.3f4b8524@.posting.google.c om...
> > > Hello
> > > > I am calling a stored procedure in a MSDE/SQLServer DB form within my
> > > Visual C++ 6.0 program along the lines
> > > CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> > > With
> > > DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> > > It all works sweet as, but it can take a while and I want to let the
> > > user abort it.
> > > Everything I've tried ends in tears.|||John Bell (jbellnewsposts@.hotmail.com) writes:
> I am not sure what you mean by killing the server. Look up the KILL
> command in books online.

And Books Online says:

KILL permissions default to the members of the sysadmin and processadmin
fixed database roles, and are not transferable.

And Mike wants to give his users away to cancel their running commands.

And killing the entire connection would be a huge overkill anyway, when
all you want to do is to cancel the current batch.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Mike Brown (browna@.beer.com) writes:
> I am calling a stored procedure in a MSDE/SQLServer DB form within my
> Visual C++ 6.0 program along the lines
> CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> With
> DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> It all works sweet as, but it can take a while and I want to let the
> user abort it.
> Everything I've tried ends in tears.

You don't say much of what you have tried. Then again, I will have to
admit that I have no experience of OLE DB Consumer templates, although
I've recently started to program against SQLOLEDB.

But I can't see but that to do this, you need to use asynchrounous
execution. The MDAC Books Online says:

Consumers that want to asynchronously open a rowset set the
DBPROPVAL_ASYNCH_INITIALIZE bit in the DBPROP_ROWSET_ASYNCH property.
When setting this bit prior to calling ICommand::Execute,
IOpenRowset::OpenRowset, IDBSchemaRowset::GetRowset,
IRowPosition::GetRowset, IColumnsRowset::GetColumnsRowset,
IMultipleResults::GetResult, ISourcesRowset::GetSourcesRowset, or any
other method that returns a rowset, riid must be set to
IID_IDBAsynchStatus, IID_IConnectionPointContainer, or IID_IUnknown.
...
To cancel creation of the rowset, the consumer can call
IDBAsynchStatus::Abort or can simply release all interfaces on the
rowset. Once the rowset's reference count goes to zero, any
asynchronous processing is canceled and the rowset is released. Calling
IDBAsynchStatus::Abort still requires releasing the interface.

If you don't do it asynchrounously... I guess you could start to
release things from another thread, but I'm not surprised if it ends
in tears...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||John was referring to the T-SQL 'KILL' command, not the unix kill command.

Aborting a thread CRASHES Sql Server 2000!

Hi,

I'm creating a new thread and executing a database import operation using a transaction. I have had several problems that I cannot explain, the most serious of them being that aborting this thread sometimes crashes the instance of Sql Server to which I was connected!

The application is a Windows Forms app, and when I launch the import operation I display a form that allows me to abort the import while it is in progress. If the user decides to abort the import in the middle of things, I call the Abort() method of the thread executing the import.

I'm catching any exceptions in the method on the bottom of the call stack (the ThreadStart delegate) and logging the information, and from the stack trace I can see that the ThreadAbortException happened to occur a few levels into the internals of the SqlCommand.ExecuteNonQuery method. This is where it is most likely to occur, because this is where the thread spends most of it's time, as the import consists of executing a bunch of large script files (often many megabytes of script text in a single round trip).

So far so good, but now things get a bit strange. The connection state is Closed when I catch the ThreadAbortException, indicating that ADO.NET code (probably ExecuteNonQuery) caught the excpetion, closed the connection and rethrew it. I would expect in this case that Sql Server would rollback the pending transaction - despite the application not issuing an explicit rollback command, since (local) transactions cannot span across connections anyway.

Instead, this completely CRASHES the Sql Server instance! It's not just a matter of locks aquired during the transaction not being released; it is no longer possible to connect to another *catalog* using Query Analyzer, or to view "current activity" using Enterprise Manager. Nor is it possible to shut down Sql Server using the management console - it just changes status to say that shutdown is in progress (I have the French version, the wording might be slightly different in the English user interface) and then nothing happens. I went to lunch to give Sql Server plenty of time to recover, but nothing changed.

In the end, I had to REBOOT the server in order to bring Sql Server back to life.

I'm using SqlClient with .NET 1.1 and Sql Server 2000 on Windows Server 2003 with all service packs and critical updates.

I should probably mention that I am NOT using SqlTransaction but instead sending "BEGIN TRANSACTION" and the corresponding commit/rollback commands to Sql Server using SqlCommand.ExecuteNonQuery. But please, do not allow this to take the focus away from the question of how to avoid Sql Server crashes, because this is NOT correct behavior on SQL Server's part (or possibly SqlClient) regardless of my application code! There is actually a reason I don't use SqlTransaction: I've no idea why the behavior is not the same, but when I used SqlTransaction and the size of the transaction becomes really large, SqlTransaciton.Rollback() invariably fails with an exception complaining that "the server did not respond". I don't know why it works when I just use text commands instead, but for the moment at least I think it is more important to focus on the crashing of Sql Server.

For now, I've had to change the abort logic so that instead of calling Thread.Abort() I just set a flag, then the thread executing the import will check this flag after each round-trip to the database and throw an exception in the event that abort has been requested. This works, i.e. everything is rolled back and sql server stays alive and all, but it does mean that the user might have to wait quite a while after requesting Abort until the program actually stops executing and rolls back the transaction.

I hope someone can give me some answers with this as I'm beginning to really lose faith in Sql Server as a reliable backend database for anything a bit demanding; completely crashing the server just because I abort a thread seems a tad fragile to be honest.

Here's the stack trace for the ThreadAbortException (it's the French-resourced version of the Framework, but this obviously shouldn't make any difference):

System.Threading.ThreadAbortException: Le thread a t abandonn.
à SNINativeMethodWrapper.SNIPacketGetConnection(IntPtr packet)
à System.Data.SqlClient.TdsParserStateObject.ProcessSniPacket(IntPtr packet, UInt32 error)
à System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
à System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
à System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
à System.Data.SqlClient.TdsParserStateObject.ReadByte()
à System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
à System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
à System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
à System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
à Import.Worker.executeSql(String sql, Boolean log)

My program catches the exception, logs it (as you can see) and continues executing (not the aborted thread!) normally, but Sql Server is dead and apparently can be revived only by rebooting the host computer...!

|||

Have you applied the lastes patch for sql server 2000? It might be a bug in the server. The following KB article might be related to your problem.

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

|||

Moving the thread to the SQL Server Engine.

|||

i saw a similar problem when aborting a backup,

the system particulars are 16P Itanium2, W2K3, SQL2000 sp3 + hotfix 1027, fiber mode.

the problem did not appear w/o fiber mode or on sp4 and later 2000 level builds

some aspects of this problem did not replicate consistently between difference systems,

Aborting a thread CRASHES Sql Server 2000!

Hi,

In the end, I had to REBOOT the server in order to bring Sql Server back to life.

I'm using SqlClient with .NET 1.1 and Sql Server 2000 on Windows Server 2003 with all service packs and critical updates.

Here's the stack trace for the ThreadAbortException (it's the French-resourced version of the Framework, but this obviously shouldn't make any difference):

|||

Have you applied the lastes patch for sql server 2000? It might be a bug in the server. The following KB article might be related to your problem.

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

|||

Moving the thread to the SQL Server Engine.

|||

i saw a similar problem when aborting a backup,

the system particulars are 16P Itanium2, W2K3, SQL2000 sp3 + hotfix 1027, fiber mode.

the problem did not appear w/o fiber mode or on sp4 and later 2000 level builds

some aspects of this problem did not replicate consistently between difference systems,

ABORT_XACT

Hi!
One of db_batch processes that was running localy on the server, failed on
our production MSSQL server, without any error neither in sql server errorlog
or job log. Using lumigent I was able to extract following information about
failed transaction:
2005-07-27
01:27:37.120|0000:004bceca|ABORT_XACT|NULL|.|.|.|0 |64|0|0|0|0000141b:00000a52:041c|user_transaction|
Server is MSSQL2000 with SP3a on W2000.
What might be the reason for this failure?
Thanks
I would try running profiler and capture all errors and warnings and
recreate. What you supplied is not much info. You may need to filter on the
dbid of the database you are having problems with.
"kimi" wrote:

> Hi!
> One of db_batch processes that was running localy on the server, failed on
> our production MSSQL server, without any error neither in sql server errorlog
> or job log. Using lumigent I was able to extract following information about
> failed transaction:
> 2005-07-27
> 01:27:37.120|0000:004bceca|ABORT_XACT|NULL|.|.|.|0 |64|0|0|0|0000141b:00000a52:041c|user_transaction|
> Server is MSSQL2000 with SP3a on W2000.
> What might be the reason for this failure?
> Thanks
sql

ABORT_XACT

Hi!
One of db_batch processes that was running localy on the server, failed on
our production MSSQL server, without any error neither in sql server errorlo
g
or job log. Using lumigent I was able to extract following information about
failed transaction:
2005-07-27
01:27:37.120|0000:004bceca|ABORT_XACT|NULL|.|.|.|0|64|0|0|0|0000141b:00000a5
2:041c|user_transaction|
Server is MSSQL2000 with SP3a on W2000.
What might be the reason for this failure?
ThanksI would try running profiler and capture all errors and warnings and
recreate. What you supplied is not much info. You may need to filter on the
dbid of the database you are having problems with.
"kimi" wrote:

> Hi!
> One of db_batch processes that was running localy on the server, failed on
> our production MSSQL server, without any error neither in sql server error
log
> or job log. Using lumigent I was able to extract following information abo
ut
> failed transaction:
> 2005-07-27
> 01:27:37.120|0000:004bceca|ABORT_XACT|NULL|.|.|.|0|64|0|0|0|0000141b:00000
a52:041c|user_transaction|
> Server is MSSQL2000 with SP3a on W2000.
> What might be the reason for this failure?
> Thanks

ABORT_XACT

Hi!
One of db_batch processes that was running localy on the server, failed on
our production MSSQL server, without any error neither in sql server errorlog
or job log. Using lumigent I was able to extract following information about
failed transaction:
2005-07-27
01:27:37.120|0000:004bceca|ABORT_XACT|NULL|.|.|.|0|64|0|0|0|0000141b:00000a52:041c|user_transaction|
Server is MSSQL2000 with SP3a on W2000.
What might be the reason for this failure?
ThanksI would try running profiler and capture all errors and warnings and
recreate. What you supplied is not much info. You may need to filter on the
dbid of the database you are having problems with.
"kimi" wrote:
> Hi!
> One of db_batch processes that was running localy on the server, failed on
> our production MSSQL server, without any error neither in sql server errorlog
> or job log. Using lumigent I was able to extract following information about
> failed transaction:
> 2005-07-27
> 01:27:37.120|0000:004bceca|ABORT_XACT|NULL|.|.|.|0|64|0|0|0|0000141b:00000a52:041c|user_transaction|
> Server is MSSQL2000 with SP3a on W2000.
> What might be the reason for this failure?
> Thanks

abort insertcommand

hey.

I have a formview - inertitemtemplate. In here I have a button with CommandName="Insert". In code behind under FormView1.ItemInserting I want to be able to abort the inserting in some cases. Is this possible?

Simple. In the event, simply do: e.Cancel = true.

Abort a long running query

Hi
I would like to have a long running query aborted by SQL server, such that,
that query does not become a resource Hog and I can catch that in my client
side code and display a nice error message to the user.
Any way I could do that?
I am sorry if this is being posted in the wrong forum
--
MattISTS wrote:
> Hi
> I would like to have a long running query aborted by SQL server,
> such that, that query does not become a resource Hog and I can catch
> that in my client side code and display a nice error message to the
> user.
> Any way I could do that?
> I am sorry if this is being posted in the wrong forum
You can set a query timeout from your client code and roll the
transaction back if you hit the timeout. But that can only be done on
the client. From the server, you can use the SET
QUERY_GOVERNOR_COST_LIMIT server option to limit queries from runnning
that are too costly.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Abort a long running query

Hi
I would like to have a long running query aborted by SQL server, such that,
that query does not become a resource Hog and I can catch that in my client
side code and display a nice error message to the user.
Any way I could do that?
I am sorry if this is being posted in the wrong forum
Matt
ISTS wrote:
> Hi
> I would like to have a long running query aborted by SQL server,
> such that, that query does not become a resource Hog and I can catch
> that in my client side code and display a nice error message to the
> user.
> Any way I could do that?
> I am sorry if this is being posted in the wrong forum
You can set a query timeout from your client code and roll the
transaction back if you hit the timeout. But that can only be done on
the client. From the server, you can use the SET
QUERY_GOVERNOR_COST_LIMIT server option to limit queries from runnning
that are too costly.
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql

Abort a long running query

Hi
I would like to have a long running query aborted by SQL server, such that,
that query does not become a resource Hog and I can catch that in my client
side code and display a nice error message to the user.
Any way I could do that?
I am sorry if this is being posted in the wrong forum
--
MattISTS wrote:
> Hi
> I would like to have a long running query aborted by SQL server,
> such that, that query does not become a resource Hog and I can catch
> that in my client side code and display a nice error message to the
> user.
> Any way I could do that?
> I am sorry if this is being posted in the wrong forum
You can set a query timeout from your client code and roll the
transaction back if you hit the timeout. But that can only be done on
the client. From the server, you can use the SET
QUERY_GOVERNOR_COST_LIMIT server option to limit queries from runnning
that are too costly.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Abnormally Large Backup Files

Hi All,
We seem to have an issue with our backup routine for one of the databases.
On a daily basis, we have one of our databases back up to disk. However, I
noticed that our backup file is huge (39GB!). I checked the database, and
it says it is only using 586MB of space. I also checked the transaction
log, and it says it is using 100MB of space.
We have a weekly backup of the database, and it only takes up 921MB, so I
can't see why the daily backup takes up so much room. Does it append to the
existing backup? If so, how can I selectively remove old backups from it?
I looked at the backup routine, and here's the SQL that is run:
BACKUP DATABASE [NAPROD] TO DISK = N'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\NAPROD daily production backup.BAK' WITH NOINIT ,
NOUNLOAD , NAME = N'NAPROD daily production backup ', NOSKIP , STATS = 10, DESCRIPTION = N'Navision production database backup ', NOFORMAT
Looking at it, I can't see any problem. I'd really like to get this file
down to something more manageable, as it is killing our backup program.
Any ideas would be appreciated! :)
Thanks!
Brian.Change the NOINIT to INIT.
--
Andrew J. Kelly
SQL Server MVP
"Brian Piotrowski" <bpiotrowski@.simcoeparts.com> wrote in message
news:%23xbCmNvRDHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> We seem to have an issue with our backup routine for one of the databases.
> On a daily basis, we have one of our databases back up to disk. However,
I
> noticed that our backup file is huge (39GB!). I checked the database, and
> it says it is only using 586MB of space. I also checked the transaction
> log, and it says it is using 100MB of space.
> We have a weekly backup of the database, and it only takes up 921MB, so I
> can't see why the daily backup takes up so much room. Does it append to
the
> existing backup? If so, how can I selectively remove old backups from it?
> I looked at the backup routine, and here's the SQL that is run:
> BACKUP DATABASE [NAPROD] TO DISK = N'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\NAPROD daily production backup.BAK' WITH NOINIT ,
> NOUNLOAD , NAME = N'NAPROD daily production backup ', NOSKIP , STATS => 10, DESCRIPTION = N'Navision production database backup ', NOFORMAT
> Looking at it, I can't see any problem. I'd really like to get this file
> down to something more manageable, as it is killing our backup program.
> Any ideas would be appreciated! :)
> Thanks!
> Brian.
>

Abnormal user connection increased on SQL server

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

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

Abnormal user connection increased on SQL server

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

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

Abnormal user connection increased on SQL server

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

ABNORMAL Transactional Replication behaviour

Actually i have set up a transactional replication with one publisher
and 1 subscriber (planning, then, to add other 6 subscribers later on).
The point is that after setting everything up, the replication works
really fine for some minutes and then just stops without any warning or
error!! The system is composed by this replication and some others
applications that writes on some tables on the subscriber. This app
starts writting data... everything works fine and then, BANG!, without
any reason data is not sent to the publisher anymore! SQL server doesn't
seems to notice that!
The replication has itself some filters and some tables have numeric
counters with identity YES (also the one that is written by the
application i mentioned before) . But, when i set up the publication i
assign a range to this counters and the replication blocks itself before
reaching the 1 % of the range i assing to this field!!
Anyone has an idea of what is happening?
Please... somebody help me!!
F.P.
what does sp_browsereplcmds reveal? Run this in the distribution database to
see if commands are pooling there.
If they aren't run sp_repltrans in your publication database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"newsgroup" <fabio.pliger@.siavr.it> wrote in message
news:nVn4f.10530$65.287427@.twister1.libero.it...
> Actually i have set up a transactional replication with one publisher and
> 1 subscriber (planning, then, to add other 6 subscribers later on). The
> point is that after setting everything up, the replication works really
> fine for some minutes and then just stops without any warning or error!!
> The system is composed by this replication and some others applications
> that writes on some tables on the subscriber. This app starts writting
> data... everything works fine and then, BANG!, without any reason data is
> not sent to the publisher anymore! SQL server doesn't seems to notice
> that!
> The replication has itself some filters and some tables have numeric
> counters with identity YES (also the one that is written by the
> application i mentioned before) . But, when i set up the publication i
> assign a range to this counters and the replication blocks itself before
> reaching the 1 % of the range i assing to this field!!
> Anyone has an idea of what is happening?
> Please... somebody help me!!
> F.P.
|||Thanks Hilary for the advice, but sp_browsereplcmds reveals only that
the last command was the one for the last record send from the
subscriber to the published database(no cmds for the records in the
subscriber and not replicated to the publisher). Then i tried to run
sp_repltrans on my publication db but it returns me no results... All
the agents continue "working" without any error, but no more records are
replicated (neither from pubisher to subscriber or vice-versa). Wherelse
can i search for the transactions failed? I mean...
Any other idea? The main thing that upsets me is that i'm not able to
see what's going wrong!! I'll try changing some configurations... I'm
using a push subscription... is it better to use a pull one?
I really need some help...
thanks...
Hilary Cotter ha scritto:
> what does ? Run this in the distribution database to
> see if commands are pooling there.
> If they aren't run sp_repltrans in your publication database.
>

Abnormal Timeout Issue on Production System

Hi all,
My production system is experiencing abnormal timeouts during posting of
transaction. The abnormal part is:
System usually works fine for about 6 w

s; but one unlucky day, suddenly
for no apparent reason, system experiences massive timeout for about 2 - 3
hours; after that everything is fine again.
A background on the production system:
? It is a sales and stock control system. With a fair number (nothing
massive) of sales transactions every operation days
? Approx 50 client users
? Developed using VB .Net, VS 2003
? Running on .Net Framework 2.0
? Running on SQL Server database
? Supports multi-user, single database
It is also running on a pretty high-end server (from what I was told) with
spec:
? HP ML 530 G2 machine
? 2-processors, each 3 GHz, running Xeon HT
? 4 GB ECC RAM
? HDD running RAID 0 and RAID 1; Total disk space 200 GB, with 72 GB free
? SQL Server 2003 standard edition with sp3
? Windows 2003 Server standard edition
? Running other s/w, mainly Norton Anti-Virus 9.0 corp. version and Verita
s
9.1
USUALLY DURING THE FINE DAYS…
System works fine during both peak and off-peak hours with very rare
timeouts. Those timeouts are caused mainly by updating of "constant" tables,
such as stock when two customers are buying the same item at the same time.
That is acceptable as data integrity on those kinds of tables must be
maintained.
Database maintenance plan is in-placed to reorganized data and index pages,
scheduled to run once a w

, during non-operation hours.
HDD defragmentation is scheduled to run twice a w

, also during
non-operation hours.
The codes for the frequently used sales modules have been optimized to
ensure that transaction control is well handled, and unnecessary queries
removed.
HOWEVER WHEN THE UNLUCKY DAY OCCURRED…
Timeout occurs when user posts a sales transaction during the peak hours.
But the volume of transaction is similar to other peak hours during other
fine days!
I tried restarting the server and the client machines and applications but
the posting still timeout.
I even tried having only one user posting a transaction but that also timeou
t!
I checked the Profiler and it reveals that SQL Server is “stuck” while
attempting to insert some rows into a few tables at the start of the
transaction. These tables have small number of rows, from 10K to 200K each.
I
repeated the same test for only one user, and SQL Server remains “stuck”
as
well. BUT during fine days, SQL Server never has problem with those tables.
It can even insert data quickly into big tables with 2M+ rows!
I also checked the SQL EM Current Activities, and can find no tables being
blocked when only one user is posting. Tables are only blocked when more tha
n
one user is posting, and that is understandable coz SQL Server is “stuck
on
the 1st user’s transaction, thus blocking everyone else.
The SQL Server just become spastic, obsessed with the 1st (or only) user
transaction, never wanting to finish it off that I know it can.
And the weird thing is after 2-3 hours, everything goes back to normal, with
SQL Server at its best again.
PUZZLED…
The server specs is top-end; The program should be well optimized as far as
the frequently used sales modules are concerned; Maintenance Plans and
defragmentation scheduled tasks are in place; Heavy jobs such as Veritas
backup are only done during non-operation hours; It works fine for about
six-w

, then it gets “stuck” on a few small tables, only to “unstuck
” after
2-3 hours break; The weirdest of all, it even timeouts when only one user
(having all the server and database resources to him /herself) is posting a
sales transaction!
Can anyone please kindly advice what are the possible causes and resolutions
for this anomalies be? Can the hardware spec affect it, even though it is
supposed to be high-end? Could other applications such as Veritas co-exist
with the SQL Server? Is SQL Server running any system jobs, e.g. Ghost
Cleanup, that can slow down its performance?
TQ in advance.Hi
The fact that it is stuck sounds like blocking issues.
If the application does not finish it's transaction on a specific set of
row(s) in a table, and another one comes in to work on the same row(s), the
2nd one has to wait.
When this happens, run sp_who2 and see what is blocking what.
Look up "blocks, avoiding" in books online.
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/
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:B1960834-C93C-458E-BC88-792045D88F2C@.microsoft.com...
> Hi all,
> My production system is experiencing abnormal timeouts during posting of
> transaction. The abnormal part is:
> System usually works fine for about 6 w

s; but one unlucky day, suddenly
> for no apparent reason, system experiences massive timeout for about 2 - 3
> hours; after that everything is fine again.
>
> A background on the production system:
> . It is a sales and stock control system. With a fair number (nothing
> massive) of sales transactions every operation days
> . Approx 50 client users
> . Developed using VB .Net, VS 2003
> . Running on .Net Framework 2.0
> . Running on SQL Server database
> . Supports multi-user, single database
> It is also running on a pretty high-end server (from what I was told) with
> spec:
> . HP ML 530 G2 machine
> . 2-processors, each 3 GHz, running Xeon HT
> . 4 GB ECC RAM
> . HDD running RAID 0 and RAID 1; Total disk space 200 GB, with 72 GB free
> . SQL Server 2003 standard edition with sp3
> . Windows 2003 Server standard edition
> . Running other s/w, mainly Norton Anti-Virus 9.0 corp. version and
> Veritas
> 9.1
>
> USUALLY DURING THE FINE DAYS.
> System works fine during both peak and off-peak hours with very rare
> timeouts. Those timeouts are caused mainly by updating of "constant"
> tables,
> such as stock when two customers are buying the same item at the same
> time.
> That is acceptable as data integrity on those kinds of tables must be
> maintained.
> Database maintenance plan is in-placed to reorganized data and index
> pages,
> scheduled to run once a w

, during non-operation hours.
> HDD defragmentation is scheduled to run twice a w

, also during
> non-operation hours.
> The codes for the frequently used sales modules have been optimized to
> ensure that transaction control is well handled, and unnecessary queries
> removed.
>
> HOWEVER WHEN THE UNLUCKY DAY OCCURRED.
> Timeout occurs when user posts a sales transaction during the peak hours.
> But the volume of transaction is similar to other peak hours during other
> fine days!
> I tried restarting the server and the client machines and applications but
> the posting still timeout.
> I even tried having only one user posting a transaction but that also
> timeout!
> I checked the Profiler and it reveals that SQL Server is "stuck" while
> attempting to insert some rows into a few tables at the start of the
> transaction. These tables have small number of rows, from 10K to 200K
> each. I
> repeated the same test for only one user, and SQL Server remains "stuck"
> as
> well. BUT during fine days, SQL Server never has problem with those
> tables.
> It can even insert data quickly into big tables with 2M+ rows!
> I also checked the SQL EM Current Activities, and can find no tables being
> blocked when only one user is posting. Tables are only blocked when more
> than
> one user is posting, and that is understandable coz SQL Server is "stuck"
> on
> the 1st user's transaction, thus blocking everyone else.
> The SQL Server just become spastic, obsessed with the 1st (or only) user
> transaction, never wanting to finish it off that I know it can.
> And the weird thing is after 2-3 hours, everything goes back to normal,
> with
> SQL Server at its best again.
>
> PUZZLED.
> The server specs is top-end; The program should be well optimized as far
> as
> the frequently used sales modules are concerned; Maintenance Plans and
> defragmentation scheduled tasks are in place; Heavy jobs such as Veritas
> backup are only done during non-operation hours; It works fine for about
> six-w

, then it gets "stuck" on a few small tables, only to "unstuck"
> after
> 2-3 hours break; The weirdest of all, it even timeouts when only one user
> (having all the server and database resources to him /herself) is posting
> a
> sales transaction!
> Can anyone please kindly advice what are the possible causes and
> resolutions
> for this anomalies be? Can the hardware spec affect it, even though it is
> supposed to be high-end? Could other applications such as Veritas co-exist
> with the SQL Server? Is SQL Server running any system jobs, e.g. Ghost
> Cleanup, that can slow down its performance?
> TQ in advance.|||Hi,
TQ for reply.
I don't think it is a blocking issue coz EM Current Activities shows no
blocks, no locks and whatsoever, and the codes have been optimized to handle
transaction properly. And as mentioned, weird things like why everything
works fine for about six w

s, but fails for 3 hours; and even
one-and-only-one user gets timeout during posting. The system just degraded
for no apparent reason.
TQ for the sp_who2 thou. Will try it out (fingers crossed) in abt six w

s
time! ;)
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> The fact that it is stuck sounds like blocking issues.
> If the application does not finish it's transaction on a specific set of
> row(s) in a table, and another one comes in to work on the same row(s), th
e
> 2nd one has to wait.
> When this happens, run sp_who2 and see what is blocking what.
> Look up "blocks, avoiding" in books online.
> 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/
> "HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
> news:B1960834-C93C-458E-BC88-792045D88F2C@.microsoft.com...
>
>

Thursday, March 29, 2012

Tuesday, March 27, 2012

Blog Archive

About Me