No, you cannot.
The datafiles have to be on a local drive -NOT a mapped drive, or even an UNC share. (A properly configured NAS/SAN is different though.)
Saturday, February 25, 2012
About the location of database file.
I can install the SQL Server Express in a computer and locate the database files in another computer of the same local network?
About the first Day and the last Day
How should I do to get the first day and the last for a given month such as '200310' or '200309' and so on ? Please help me !select dateadd(dd,-1*datepart(dd,@.d)-1,@.d)
select dateadd(mm,1,dateadd(dd,-1*datepart(dd,@.d)-1,@.d))|||it is not working!!!!!!!!!!!
I tried it
cyrus|||Can u specify ur question properly??
First day for any month is always 1 and tnow the question is to find a last day of a month that can be done!!!!!
but can u tell what u want
Cyrus|||If I past a string '200309',it should return the '20030901' and '20030930' to me. My question is something like the description above. The starting string for a month can be variable. Is all above clear to describe my question ?|||This two statement will solve ur problem
for last day
select dateadd(mm,1,dateadd(dd,-1,'200310'+'01'))
for first day
select '200310'+'01'
if u require explanation tell me|||Thanks a lot, Cyrus! I will try the statements you just show me. I have just resolved my problem to use a While loop. I left the procedure on my computer, I will show you the statements tomorrow. Thank you again for your help.|||Originally posted by cyrus
it is not working!!!!!!!!!!!
I tried it
cyrus
DECLARE @.d int
SELECT @.d = 1
select dateadd(dd,-1*datepart(dd,@.d)-1,@.d)
select dateadd(mm,1,dateadd(dd,-1*datepart(dd,@.d)-1,@.d))
You need to supply the local variable
Now try it...(and please cut and paste)|||Mr. Brett Kaiser
thanks for ur advice.
I have executed the query successfully without u help and then reached a conclusion that the query was not working right.
Thanks for ur unneedful advice
Cyrus|||I am sorry to reply so late. The statements I wrote yesterday as below:
declare @.startday char(8)
declare @.startdate datetime
declare @.enddate datetime
DECLARE @.DAILY CHAR(2)
declare @.today datetime
DECLARE @.NEXTDAY DATETIME
-- @.ym is the variable of Year_Month in yyyymm
set @.startday = @.ym + '01'
set @.startdate = convert(datetime,@.startday)
-- Is there any month not starting with '01' ?
set @.startdate = convert(char(10),@.startdate,121) + ' 07:00:00'
set @.today = @.startdate
while month(@.today) <= month(@.startdate)
begin
set @.today = @.today + 1
end
set @.enddate = convert(char(10),@.today,121) + ' 07:00:00'
Select date_mark from history where some_date >= @.startdate and some_date < @.enddate
Is there any advice from you ? Thanks again, and also thanks to Kaiser.|||What do u want to do mosu ?
Is ur query trying to find records between start date and end date?
Cyrus|||Hi,Cyrus,
What you think is exactly correct. I try to create a report what's duration is one month given by user. It is unknown what month will be selected until user selects one.
By the way, I had tried your method yesterday. The result is as follow.
select dateadd(mm,1,dateadd(dd,-1,'200310'+'01'))
the result it returned is '2003-10-30 00:00:00.000' !
It will not give me the last day of October, I think it should be '2003-10-31 00:00:00'. But I still thank you for your patience to try out my problem so enthusiastically.|||the reason you got october 30 is because that formula is flawed
it started with '200310'+'01', then subtracted a day, then added a month
what you should to do get the last day of the current month is start with '200310'+'01', then add a month, then subtract a day
easy, innit
rudy|||its this mosu.
select dateadd(dd,-1,dateadd(mm,1,'200302'+'01'))
hope this is right|||You can put a condition as follow
But here take care that month is always two digit so concat '0' when it is singel digit
where ltrim(str(year(startdate))) + ltrim(str(month(startdate))) ='200203'|||Thanks again,Cyrus. And also thanks to Rudy.Both you resolve my confuse. I was too fool to read the statement. Without Rudy's explanation, I got nothing from Cyrus.I hope I can learn so much as you two.
Now I got another question that makes me headache! I will post it later and hope both you would help me again.
select dateadd(mm,1,dateadd(dd,-1*datepart(dd,@.d)-1,@.d))|||it is not working!!!!!!!!!!!
I tried it
cyrus|||Can u specify ur question properly??
First day for any month is always 1 and tnow the question is to find a last day of a month that can be done!!!!!
but can u tell what u want
Cyrus|||If I past a string '200309',it should return the '20030901' and '20030930' to me. My question is something like the description above. The starting string for a month can be variable. Is all above clear to describe my question ?|||This two statement will solve ur problem
for last day
select dateadd(mm,1,dateadd(dd,-1,'200310'+'01'))
for first day
select '200310'+'01'
if u require explanation tell me|||Thanks a lot, Cyrus! I will try the statements you just show me. I have just resolved my problem to use a While loop. I left the procedure on my computer, I will show you the statements tomorrow. Thank you again for your help.|||Originally posted by cyrus
it is not working!!!!!!!!!!!
I tried it
cyrus
DECLARE @.d int
SELECT @.d = 1
select dateadd(dd,-1*datepart(dd,@.d)-1,@.d)
select dateadd(mm,1,dateadd(dd,-1*datepart(dd,@.d)-1,@.d))
You need to supply the local variable
Now try it...(and please cut and paste)|||Mr. Brett Kaiser
thanks for ur advice.
I have executed the query successfully without u help and then reached a conclusion that the query was not working right.
Thanks for ur unneedful advice
Cyrus|||I am sorry to reply so late. The statements I wrote yesterday as below:
declare @.startday char(8)
declare @.startdate datetime
declare @.enddate datetime
DECLARE @.DAILY CHAR(2)
declare @.today datetime
DECLARE @.NEXTDAY DATETIME
-- @.ym is the variable of Year_Month in yyyymm
set @.startday = @.ym + '01'
set @.startdate = convert(datetime,@.startday)
-- Is there any month not starting with '01' ?
set @.startdate = convert(char(10),@.startdate,121) + ' 07:00:00'
set @.today = @.startdate
while month(@.today) <= month(@.startdate)
begin
set @.today = @.today + 1
end
set @.enddate = convert(char(10),@.today,121) + ' 07:00:00'
Select date_mark from history where some_date >= @.startdate and some_date < @.enddate
Is there any advice from you ? Thanks again, and also thanks to Kaiser.|||What do u want to do mosu ?
Is ur query trying to find records between start date and end date?
Cyrus|||Hi,Cyrus,
What you think is exactly correct. I try to create a report what's duration is one month given by user. It is unknown what month will be selected until user selects one.
By the way, I had tried your method yesterday. The result is as follow.
select dateadd(mm,1,dateadd(dd,-1,'200310'+'01'))
the result it returned is '2003-10-30 00:00:00.000' !
It will not give me the last day of October, I think it should be '2003-10-31 00:00:00'. But I still thank you for your patience to try out my problem so enthusiastically.|||the reason you got october 30 is because that formula is flawed
it started with '200310'+'01', then subtracted a day, then added a month
what you should to do get the last day of the current month is start with '200310'+'01', then add a month, then subtract a day
easy, innit
rudy|||its this mosu.
select dateadd(dd,-1,dateadd(mm,1,'200302'+'01'))
hope this is right|||You can put a condition as follow
But here take care that month is always two digit so concat '0' when it is singel digit
where ltrim(str(year(startdate))) + ltrim(str(month(startdate))) ='200203'|||Thanks again,Cyrus. And also thanks to Rudy.Both you resolve my confuse. I was too fool to read the statement. Without Rudy's explanation, I got nothing from Cyrus.I hope I can learn so much as you two.
Now I got another question that makes me headache! I will post it later and hope both you would help me again.
about the e-mail question of Sql Server 2005 notification services
Dear all
can notification services send a e-mail with a accessory of PDF file ?
Tanks a lot.
Out of the box, SSNS comes with a SMTP delivery channel that does not support attachments. However, you can easily create your own custom event provider for this.Or, the notification can send a hyperlink to a PDF file that's available online.
HTH...
Joe|||Hi Joe
Thank you for your help.
about the e-mail question of Sql Server 2005 notification services
Dear all
can notification services send a e-mail with a accessory of PDF file ?
Tanks a lot.
Out of the box, SSNS comes with a SMTP delivery channel that does not support attachments. However, you can easily create your own custom event provider for this.Or, the notification can send a hyperlink to a PDF file that's available online.
HTH...
Joe|||Hi Joe
Thank you for your help.
About The Connect to Server Dialog
In server name column, it apears all the server name that i uesed before, but some of them are not available now, how do I remove them?
There isn't a way to be very surgical about this, but you can use the "big hammer" approach and delete the mru.dat file in C:\Documents and Settings\{you}\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell. This obliterates the most-recently-used list of servers. The connection dialog will create a new mru.dat file the next time you connect to a server.
About the Complex SQL Statement
Belows are the data of table T1
field1 field2 field3 Value
F1 F2 F3 A
F1 F2 F3 B
F1 F2 F3 C
... ... ....
F1 F2 F3 Z
Can any bright person help to script the SQL to extract above data set and present as below ?
field1 field2 field3 Value
F1 F2 F3 ABC.....Z
Thanks for helpThought this woiuld be a good exercise...WHY you would want to do this, I have no idea...
Sorry, had to use a cursor....any non cursor solutions out there?
USE Northwind
GO
CREATE TABLE myTable99(Col1 char(2), Col2 char(2), Col3 char(2), Col4 char(1))
GO
INSERT INTO myTable99(Col1, Col2, Col3, Col4)
SELECT 'F1', 'F2', 'F3', 'A' UNION ALL
SELECT 'F1', 'F2', 'F3', 'B' UNION ALL
SELECT 'F1', 'F2', 'F3', 'C' UNION ALL
SELECT 'F1', 'F2', 'F3', 'Z' UNION ALL
SELECT 'F1', 'F2', 'F5', 'A' UNION ALL
SELECT 'F1', 'F2', 'F6', 'B' UNION ALL
SELECT 'F1', 'F2', 'F7', 'C' UNION ALL
SELECT 'F1', 'F2', 'F8', 'Z'
GO
SET NOCOUNT ON
DECLARE myCursor99 CURSOR
FOR
SELECT DISTINCT Col1, Col2, Col3
FROM myTable99
DECLARE @.Col1 char(2), @.Col2 char(2), @.Col3 char(2), @.Col4 varchar(7000)
DECLARE @.x TABLE(Col1 char(2), Col2 char(2), Col3 char(2), Col4 varchar(7000))
OPEN myCursor99
FETCH NEXT
FROM myCursor99
INTO @.Col1
, @.Col2
, @.Col3
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.Col4 = COALESCE(@.Col4+ ', ', '') + CAST(Col4 AS varchar(6))
FROM myTable99
WHERE Col1 = @.Col1
AND Col2 = @.Col2
AND Col3 = @.Col3
INSERT INTO @.x(Col1, Col2, Col3, Col4)
SELECT DISTINCT @.Col1, @.Col2, @.Col3, @.Col4
FROM myTable99
WHERE Col1 = @.Col1
AND Col2 = @.Col2
AND Col3 = @.Col3
FETCH NEXT
FROM myCursor99
INTO @.Col1
, @.Col2
, @.Col3
SELECT @.Col4 = null
END
CLOSE myCursor99
DEALLOCATE myCursor99
SET NOCOUNT OFF
SELECT * FROM @.x
SET NOCOUNT OFF
GO
DROP TABLE myTable99
GO|||CREATE FUNCTION dbo.f_ConcatVals(@.f1 char(2), @.f2 char(2), @.f3 char(3)) RETURNS varchar(8000) AS BEGIN
DECLARE @.s varchar(8000)
SET @.s = ''
SELECT @.s = @.s + Value
FROM T1
WHERE Field1 = @.f1 AND Field2 = @.f2 AND Field3 = @.f3
ORDER BY Value
RETURN @.s END
SELECT DISTINCT Field1, Field2, Field3, dbo.f_ConcatVals(Field1,Field2,Field3) Value
FROM t1
field1 field2 field3 Value
F1 F2 F3 A
F1 F2 F3 B
F1 F2 F3 C
... ... ....
F1 F2 F3 Z
Can any bright person help to script the SQL to extract above data set and present as below ?
field1 field2 field3 Value
F1 F2 F3 ABC.....Z
Thanks for helpThought this woiuld be a good exercise...WHY you would want to do this, I have no idea...
Sorry, had to use a cursor....any non cursor solutions out there?
USE Northwind
GO
CREATE TABLE myTable99(Col1 char(2), Col2 char(2), Col3 char(2), Col4 char(1))
GO
INSERT INTO myTable99(Col1, Col2, Col3, Col4)
SELECT 'F1', 'F2', 'F3', 'A' UNION ALL
SELECT 'F1', 'F2', 'F3', 'B' UNION ALL
SELECT 'F1', 'F2', 'F3', 'C' UNION ALL
SELECT 'F1', 'F2', 'F3', 'Z' UNION ALL
SELECT 'F1', 'F2', 'F5', 'A' UNION ALL
SELECT 'F1', 'F2', 'F6', 'B' UNION ALL
SELECT 'F1', 'F2', 'F7', 'C' UNION ALL
SELECT 'F1', 'F2', 'F8', 'Z'
GO
SET NOCOUNT ON
DECLARE myCursor99 CURSOR
FOR
SELECT DISTINCT Col1, Col2, Col3
FROM myTable99
DECLARE @.Col1 char(2), @.Col2 char(2), @.Col3 char(2), @.Col4 varchar(7000)
DECLARE @.x TABLE(Col1 char(2), Col2 char(2), Col3 char(2), Col4 varchar(7000))
OPEN myCursor99
FETCH NEXT
FROM myCursor99
INTO @.Col1
, @.Col2
, @.Col3
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.Col4 = COALESCE(@.Col4+ ', ', '') + CAST(Col4 AS varchar(6))
FROM myTable99
WHERE Col1 = @.Col1
AND Col2 = @.Col2
AND Col3 = @.Col3
INSERT INTO @.x(Col1, Col2, Col3, Col4)
SELECT DISTINCT @.Col1, @.Col2, @.Col3, @.Col4
FROM myTable99
WHERE Col1 = @.Col1
AND Col2 = @.Col2
AND Col3 = @.Col3
FETCH NEXT
FROM myCursor99
INTO @.Col1
, @.Col2
, @.Col3
SELECT @.Col4 = null
END
CLOSE myCursor99
DEALLOCATE myCursor99
SET NOCOUNT OFF
SELECT * FROM @.x
SET NOCOUNT OFF
GO
DROP TABLE myTable99
GO|||CREATE FUNCTION dbo.f_ConcatVals(@.f1 char(2), @.f2 char(2), @.f3 char(3)) RETURNS varchar(8000) AS BEGIN
DECLARE @.s varchar(8000)
SET @.s = ''
SELECT @.s = @.s + Value
FROM T1
WHERE Field1 = @.f1 AND Field2 = @.f2 AND Field3 = @.f3
ORDER BY Value
RETURN @.s END
SELECT DISTINCT Field1, Field2, Field3, dbo.f_ConcatVals(Field1,Field2,Field3) Value
FROM t1
about text ,ntext,image error message
hello all:
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
I don't know why and how this happend .who can help me ,thx.
Hi
"lxg" wrote:
> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
I don't know why and how this happend .who can help me ,thx.
Hi
"lxg" wrote:
> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:5 5processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John
about text ,ntext,image error message
hello all:
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
I don't know why and how this happend .who can help me ,thx.Hi
"lxg" wrote:
> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
I don't know why and how this happend .who can help me ,thx.Hi
"lxg" wrote:
> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John
about text ,ntext,image error message
hello all:
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
I don't know why and how this happend .who can help me ,thx.Hi
"lxg" wrote:
> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John
my sql server is msde 2000,no sp4 & win xp
when inster data there are some error like this:
location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
or when delete date :error like this:
location(1:3568).slot 0 for text,ntext,or image node does not exist.
or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
I don't know why and how this happend .who can help me ,thx.Hi
"lxg" wrote:
> hello all:
> my sql server is msde 2000,no sp4 & win xp
> when inster data there are some error like this:
> location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> or when delete date :error like this:
> location(1:3568).slot 0 for text,ntext,or image node does not exist.
> or location:rebase.cpp:1371Expression:m_nvars>ospid:55processid:392
> I don't know why and how this happend .who can help me ,thx.
>
The error you are getting are from a MFC class, have your run a DBCC CHECKDB
to check the database is ok?
John
About TempDB
TempDB is one of the databases equipped with MSSQL Server by default.
What is the purpose of it?
Why do we use this temp database?
--Create a temp table
CREATE TABLE #TempTable112233 (somecol INT)
--You can now see it in tempdb's sysobjects table
SELECT *
FROM tempdb..sysobjects
WHERE NAME LIKE '#TempTable112233%'
B) Many operations in SQL Server require temporary/intermediate tablesand/or sorts. For instance, if you select some data from twotables, part of the data from one of the tables may be sorted beforebeing joined with the data in the other table. That sort canoccur in tempdb.
|||The Temp DB in SQL Server 2000 is used for complex intermediate operations and can also be used to as the database to test stored procs if there is no database and tables to test them against because you don't need a database or tables to create stored procs, assuming the feature was not altered with a service pack. There two types of temp tables local with one # sign and global with two ##, the local is the most used but if your operation is long the global is more durable but it has performance impact so you have to know when to use them. Hope this helps.|||I understand from our DBA that tempdb is used to generate a templatefor a new database? Just that I caught a bit of a wigging forcreating a table without the # prefix to its name in that location, andwas told that the tempdb contributed the layout of new databases andother important things.
So what I should like to know is -- is tempdb purely a scratch area, or is it used for several other purposes?
--
My Alias
|||
My Alias wrote:
I understand from our DBA that tempdb is used to generate a template for a new database? Just that I caught a bit of a wigging for creating a table without the # prefix to its name in that location, and was told that the tempdb contributed the layout of new databases and other important things.
So what I should like to know is -- is tempdb purely a scratch area, or is it used for several other purposes?
--
My Alias
Your DBA is wrong Model DB is the template while TempDB is an intermediate or staging place to create tables and run code. Hope this helps.
about tempdb
Guys,
I have question regarding tempdb. I have several dbs in my server and size
is totally 22G. I checked the tempdb, it is roughly 6G, is it normal? In
addition, for replication, where can I find snapshot agent folder? because I
want to change the folder, how can I do that? Thanks.Hello,
Tempdb - 6GB
This is normal. If you have temporary operations like ORDER BY, GROUP BY or
Temporary table creation then it is usual that TEMPDB will grow.
But I recommend you to size the tempdb based on your requirement; Autogrowth
on tempdb will cause more resource utilization.
I have not moved a snapshot folder and hence I can not comment on this
Thanks
Hari
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:1C90F76D-7C73-4866-9E30-CB9110182D93@.microsoft.com...
> Guys,
> I have question regarding tempdb. I have several dbs in my server and size
> is totally 22G. I checked the tempdb, it is roughly 6G, is it normal? In
> addition, for replication, where can I find snapshot agent folder? because
> I
> want to change the folder, how can I do that? Thanks.
>
I have question regarding tempdb. I have several dbs in my server and size
is totally 22G. I checked the tempdb, it is roughly 6G, is it normal? In
addition, for replication, where can I find snapshot agent folder? because I
want to change the folder, how can I do that? Thanks.Hello,
Tempdb - 6GB
This is normal. If you have temporary operations like ORDER BY, GROUP BY or
Temporary table creation then it is usual that TEMPDB will grow.
But I recommend you to size the tempdb based on your requirement; Autogrowth
on tempdb will cause more resource utilization.
I have not moved a snapshot folder and hence I can not comment on this
Thanks
Hari
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:1C90F76D-7C73-4866-9E30-CB9110182D93@.microsoft.com...
> Guys,
> I have question regarding tempdb. I have several dbs in my server and size
> is totally 22G. I checked the tempdb, it is roughly 6G, is it normal? In
> addition, for replication, where can I find snapshot agent folder? because
> I
> want to change the folder, how can I do that? Thanks.
>
About tempdb
Hi
Being new to SQL server I would like some information regarding the tempdb database.
I user SQL Server 2000.
At present I appear to be backing up my tempdb but it has been failing since way back. It gives me this error when trying to bakup the database:
Executed as user: NT AUTHORITY\SYSTEM. Backup and restore operations are not allowed on database tempdb. [SQLSTATE 42000] (Error 3147) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Can someone give me a basic grounding as to what the tempdb is used for and should I back it up or not?
Thanks in advance
Neil.Sorry ignore this post
Being new to SQL server I would like some information regarding the tempdb database.
I user SQL Server 2000.
At present I appear to be backing up my tempdb but it has been failing since way back. It gives me this error when trying to bakup the database:
Executed as user: NT AUTHORITY\SYSTEM. Backup and restore operations are not allowed on database tempdb. [SQLSTATE 42000] (Error 3147) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Can someone give me a basic grounding as to what the tempdb is used for and should I back it up or not?
Thanks in advance
Neil.Sorry ignore this post
about tempdb
Guys,
I have question regarding tempdb. I have several dbs in my server and size
is totally 22G. I checked the tempdb, it is roughly 6G, is it normal? In
addition, for replication, where can I find snapshot agent folder? because I
want to change the folder, how can I do that? Thanks.
Hello,
Tempdb - 6GB
This is normal. If you have temporary operations like ORDER BY, GROUP BY or
Temporary table creation then it is usual that TEMPDB will grow.
But I recommend you to size the tempdb based on your requirement; Autogrowth
on tempdb will cause more resource utilization.
I have not moved a snapshot folder and hence I can not comment on this
Thanks
Hari
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:1C90F76D-7C73-4866-9E30-CB9110182D93@.microsoft.com...
> Guys,
> I have question regarding tempdb. I have several dbs in my server and size
> is totally 22G. I checked the tempdb, it is roughly 6G, is it normal? In
> addition, for replication, where can I find snapshot agent folder? because
> I
> want to change the folder, how can I do that? Thanks.
>
I have question regarding tempdb. I have several dbs in my server and size
is totally 22G. I checked the tempdb, it is roughly 6G, is it normal? In
addition, for replication, where can I find snapshot agent folder? because I
want to change the folder, how can I do that? Thanks.
Hello,
Tempdb - 6GB
This is normal. If you have temporary operations like ORDER BY, GROUP BY or
Temporary table creation then it is usual that TEMPDB will grow.
But I recommend you to size the tempdb based on your requirement; Autogrowth
on tempdb will cause more resource utilization.
I have not moved a snapshot folder and hence I can not comment on this
Thanks
Hari
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:1C90F76D-7C73-4866-9E30-CB9110182D93@.microsoft.com...
> Guys,
> I have question regarding tempdb. I have several dbs in my server and size
> is totally 22G. I checked the tempdb, it is roughly 6G, is it normal? In
> addition, for replication, where can I find snapshot agent folder? because
> I
> want to change the folder, how can I do that? Thanks.
>
about system variable
i write a store procedure and it include
a lot of
insert into table1(col1,col2..)
select clo1,col2.. from table2
and i want get the @.@.error and @.@.rowcount
but if i write set @.myErr=@.@.error the @.@.rowcount will
turn to 1
and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
will ture into the error of 'set @.myRowcount=@.@.rowcount'
how can i get then both?thanksTry: SELECT @.myErr = @.@.ERROR, @.myRowCount = @.@.ROWCOUN T
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>|||Hi Frank
This was already answered in another newsgroup. Please do not post the same
question independently to multiple groups, so someone doesn't waste time
answering something that has already been answered.
Thanks
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>
a lot of
insert into table1(col1,col2..)
select clo1,col2.. from table2
and i want get the @.@.error and @.@.rowcount
but if i write set @.myErr=@.@.error the @.@.rowcount will
turn to 1
and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
will ture into the error of 'set @.myRowcount=@.@.rowcount'
how can i get then both?thanksTry: SELECT @.myErr = @.@.ERROR, @.myRowCount = @.@.ROWCOUN T
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>|||Hi Frank
This was already answered in another newsgroup. Please do not post the same
question independently to multiple groups, so someone doesn't waste time
answering something that has already been answered.
Thanks
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>
About sys.fn_indexinfo in SQL2005 MOC 2733A
Hi guys,
i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
function in a module 5 but when i run a sys.fn_indexinfo how indicated a
error showed
Seems that not exist ... it's possible ? It was replaced ?
If so how i can view a index defrag status ?
Thanks in advance.You might have an version of the course that was based on a beta of the product. My guess is that
the function you want is sys.dm_db_index_physical_stats.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com@.newsgroup.nospam> wrote in message news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
> Hi guys,
> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo" function in a module 5 but
> when i run a sys.fn_indexinfo how indicated a error showed
> Seems that not exist ... it's possible ? It was replaced ?
> If so how i can view a index defrag status ?
> Thanks in advance.
>|||Yes it's right.
Found!
bye.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
> You might have an version of the course that was based on a beta of the
> product. My guess is that the function you want is
> sys.dm_db_index_physical_stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <io.com@.newsgroup.nospam> wrote in message
> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>> Hi guys,
>> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
>> function in a module 5 but when i run a sys.fn_indexinfo how indicated a
>> error showed
>> Seems that not exist ... it's possible ? It was replaced ?
>> If so how i can view a index defrag status ?
>> Thanks in advance.
>>
>|||Hi IO,
So far as I know, number of information in MOC 2733 is changed since it is
based on SQL 2005 beta. If you have any other questions, feel free to post
in our newsgroup. We are glad to provide assistance.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: <io.com@.newsgroup.nospam>
>>References: <O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl>
<uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl>
>>Subject: Re: About sys.fn_indexinfo in SQL2005 MOC 2733A
>>Date: Wed, 30 Aug 2006 16:50:21 +0200
>>Lines: 36
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
>>Message-ID: <#09RgOEzGHA.1536@.TK2MSFTNGP02.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 62.241.4.149
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP02.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:443733
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Yes it's right.
>>Found!
>>bye.
>>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
>>message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
>> You might have an version of the course that was based on a beta of the
>> product. My guess is that the function you want is
>> sys.dm_db_index_physical_stats.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <io.com@.newsgroup.nospam> wrote in message
>> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>> Hi guys,
>> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
>> function in a module 5 but when i run a sys.fn_indexinfo how indicated
a
>> error showed
>> Seems that not exist ... it's possible ? It was replaced ?
>> If so how i can view a index defrag status ?
>> Thanks in advance.
>>
>>
>>
i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
function in a module 5 but when i run a sys.fn_indexinfo how indicated a
error showed
Seems that not exist ... it's possible ? It was replaced ?
If so how i can view a index defrag status ?
Thanks in advance.You might have an version of the course that was based on a beta of the product. My guess is that
the function you want is sys.dm_db_index_physical_stats.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com@.newsgroup.nospam> wrote in message news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
> Hi guys,
> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo" function in a module 5 but
> when i run a sys.fn_indexinfo how indicated a error showed
> Seems that not exist ... it's possible ? It was replaced ?
> If so how i can view a index defrag status ?
> Thanks in advance.
>|||Yes it's right.
Found!
bye.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
> You might have an version of the course that was based on a beta of the
> product. My guess is that the function you want is
> sys.dm_db_index_physical_stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <io.com@.newsgroup.nospam> wrote in message
> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>> Hi guys,
>> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
>> function in a module 5 but when i run a sys.fn_indexinfo how indicated a
>> error showed
>> Seems that not exist ... it's possible ? It was replaced ?
>> If so how i can view a index defrag status ?
>> Thanks in advance.
>>
>|||Hi IO,
So far as I know, number of information in MOC 2733 is changed since it is
based on SQL 2005 beta. If you have any other questions, feel free to post
in our newsgroup. We are glad to provide assistance.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: <io.com@.newsgroup.nospam>
>>References: <O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl>
<uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl>
>>Subject: Re: About sys.fn_indexinfo in SQL2005 MOC 2733A
>>Date: Wed, 30 Aug 2006 16:50:21 +0200
>>Lines: 36
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
>>Message-ID: <#09RgOEzGHA.1536@.TK2MSFTNGP02.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 62.241.4.149
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP02.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:443733
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Yes it's right.
>>Found!
>>bye.
>>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
>>message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
>> You might have an version of the course that was based on a beta of the
>> product. My guess is that the function you want is
>> sys.dm_db_index_physical_stats.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <io.com@.newsgroup.nospam> wrote in message
>> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>> Hi guys,
>> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
>> function in a module 5 but when i run a sys.fn_indexinfo how indicated
a
>> error showed
>> Seems that not exist ... it's possible ? It was replaced ?
>> If so how i can view a index defrag status ?
>> Thanks in advance.
>>
>>
>>
About sys.fn_indexinfo in SQL2005 MOC 2733A
Hi guys,
i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
function in a module 5 but when i run a sys.fn_indexinfo how indicated a
error showed
Seems that not exist ... it's possible ? It was replaced ?
If so how i can view a index defrag status ?
Thanks in advance.You might have an version of the course that was based on a beta of the prod
uct. My guess is that
the function you want is sys.dm_db_index_physical_stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com@.newsgroup.nospam> wrote in message news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...[vbc
ol=seagreen]
> Hi guys,
> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo" funct
ion in a module 5 but
> when i run a sys.fn_indexinfo how indicated a error showed
> Seems that not exist ... it's possible ? It was replaced ?
> If so how i can view a index defrag status ?
> Thanks in advance.
>[/vbcol]|||Yes it's right.
Found!
bye.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
> You might have an version of the course that was based on a beta of the
> product. My guess is that the function you want is
> sys.dm_db_index_physical_stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <io.com@.newsgroup.nospam> wrote in message
> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>|||Hi IO,
So far as I know, number of information in MOC 2733 is changed since it is
based on SQL 2005 beta. If you have any other questions, feel free to post
in our newsgroup. We are glad to provide assistance.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl>[vbcol=seagreen]
in[vbcol=seagreen]
a[vbcol=seagreen]
i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
function in a module 5 but when i run a sys.fn_indexinfo how indicated a
error showed
Seems that not exist ... it's possible ? It was replaced ?
If so how i can view a index defrag status ?
Thanks in advance.You might have an version of the course that was based on a beta of the prod
uct. My guess is that
the function you want is sys.dm_db_index_physical_stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com@.newsgroup.nospam> wrote in message news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...[vbc
ol=seagreen]
> Hi guys,
> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo" funct
ion in a module 5 but
> when i run a sys.fn_indexinfo how indicated a error showed
> Seems that not exist ... it's possible ? It was replaced ?
> If so how i can view a index defrag status ?
> Thanks in advance.
>[/vbcol]|||Yes it's right.
Found!
bye.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
> You might have an version of the course that was based on a beta of the
> product. My guess is that the function you want is
> sys.dm_db_index_physical_stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <io.com@.newsgroup.nospam> wrote in message
> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>|||Hi IO,
So far as I know, number of information in MOC 2733 is changed since it is
based on SQL 2005 beta. If you have any other questions, feel free to post
in our newsgroup. We are glad to provide assistance.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl>[vbcol=seagreen]
in[vbcol=seagreen]
a[vbcol=seagreen]
About Support Policy of Database Mirror
Hi, does any body knew about whether the database mirror was supported after
the SP1 released? Thanks!Hi,
Yes I believe that database mirroring is now supported in production
environments. The below links may be of interest:
http://www.microsoft.com/presspass/press/2006/apr06/04-19SQLExpands06PR.mspx
http://blogs.msdn.com/euanga/archive/2006/04/19/578792.aspx
Hope that helps a bit
the SP1 released? Thanks!Hi,
Yes I believe that database mirroring is now supported in production
environments. The below links may be of interest:
http://www.microsoft.com/presspass/press/2006/apr06/04-19SQLExpands06PR.mspx
http://blogs.msdn.com/euanga/archive/2006/04/19/578792.aspx
Hope that helps a bit
About Support Policy of Database Mirror
Hi, does any body knew about whether the database mirror was supported after
the SP1 released? Thanks!Hi,
Yes I believe that database mirroring is now supported in production
environments. The below links may be of interest:
http://www.microsoft.com/presspass/...xpands06PR.mspx
http://blogs.msdn.com/euanga/archiv.../19/578792.aspx
Hope that helps a bit
the SP1 released? Thanks!Hi,
Yes I believe that database mirroring is now supported in production
environments. The below links may be of interest:
http://www.microsoft.com/presspass/...xpands06PR.mspx
http://blogs.msdn.com/euanga/archiv.../19/578792.aspx
Hope that helps a bit
about supersocket failed
Hi!
When i install a layered SPI, the SQL will report supersocket failed while
bind to port 1433 ? when i uninstall the layered SPI, it is OK. but i
disable the SPI by move the SPI down, it also report failed? who can tell me
the reason and how to avoid it?
Thanks!
Xiaorun huangHi
Have you run netstat -b to see what is using the port?
John
"xiaorun" wrote:
> Hi!
> When i install a layered SPI, the SQL will report supersocket failed while
> bind to port 1433 ? when i uninstall the layered SPI, it is OK. but i
> disable the SPI by move the SPI down, it also report failed? who can tell
me
> the reason and how to avoid it?
> Thanks!
> Xiaorun huang
>
>|||I had see and it is sqlservr use the 1433 port, and i can connect to 1433
port and use SQL server, but it has this error in the event log.
Thanks!
Xiaorun huang
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CE72090D-0F89-4FFF-B98E-6E7620DE129A@.microsoft.com...
> Hi
> Have you run netstat -b to see what is using the port?
> John
> "xiaorun" wrote:
>|||Hi
You can get the error message whe n the account that the SQL Server service
is running as, does not have local admin rights, but how that would be linke
d
to loading SPI and why you would not get it without, I am not sure.
John
"xiaorun" wrote:
> I had see and it is sqlservr use the 1433 port, and i can connect to 1433
> port and use SQL server, but it has this error in the event log.
> Thanks!
> Xiaorun huang
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:CE72090D-0F89-4FFF-B98E-6E7620DE129A@.microsoft.com...
>
>
When i install a layered SPI, the SQL will report supersocket failed while
bind to port 1433 ? when i uninstall the layered SPI, it is OK. but i
disable the SPI by move the SPI down, it also report failed? who can tell me
the reason and how to avoid it?
Thanks!
Xiaorun huangHi
Have you run netstat -b to see what is using the port?
John
"xiaorun" wrote:
> Hi!
> When i install a layered SPI, the SQL will report supersocket failed while
> bind to port 1433 ? when i uninstall the layered SPI, it is OK. but i
> disable the SPI by move the SPI down, it also report failed? who can tell
me
> the reason and how to avoid it?
> Thanks!
> Xiaorun huang
>
>|||I had see and it is sqlservr use the 1433 port, and i can connect to 1433
port and use SQL server, but it has this error in the event log.
Thanks!
Xiaorun huang
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CE72090D-0F89-4FFF-B98E-6E7620DE129A@.microsoft.com...
> Hi
> Have you run netstat -b to see what is using the port?
> John
> "xiaorun" wrote:
>|||Hi
You can get the error message whe n the account that the SQL Server service
is running as, does not have local admin rights, but how that would be linke
d
to loading SPI and why you would not get it without, I am not sure.
John
"xiaorun" wrote:
> I had see and it is sqlservr use the 1433 port, and i can connect to 1433
> port and use SQL server, but it has this error in the event log.
> Thanks!
> Xiaorun huang
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:CE72090D-0F89-4FFF-B98E-6E7620DE129A@.microsoft.com...
>
>
about subsuery of tsql (or anything like oracle's sequence in sql server )
exec('insert
into '+@.DestName+ '(AF_nFieldID,AF_nPublicationID,AF_nEditi
onID,AF_nAdTypeID,AF_strCode,AF_strDescription,AF_nSortID,A
F_cIfValid)'+
'select (select isnull(max(AF_nFieldID),0)+1
from '+@.DestName+'),SPD_nPubMask,SPD_nEditionMask,SPD_nGrid
ID,SPD_strPageID,SPD_strDescription,SPD_nSortNum,SPD_bIsVal
id from '+@.SourceName )
i want the subsqury (select isnull(max(AF_nFieldID),0)+1
from '+@.DestName+') make a new auto increase ID every
insert,but the sub query only make one ID,or any method
like oracle's sequence?thanksLook at identity in bol.
It will increment the field for every record inserted.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||thank you,i know the identity ,
but my question is that the destinatin table already have
data,and can't change the field to idenity
>--Original Message--
>Look at identity in bol.
>It will increment the field for every record inserted.
>Nigel Rivett
>www.nigelrivett.net
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>
into '+@.DestName+ '(AF_nFieldID,AF_nPublicationID,AF_nEditi
onID,AF_nAdTypeID,AF_strCode,AF_strDescription,AF_nSortID,A
F_cIfValid)'+
'select (select isnull(max(AF_nFieldID),0)+1
from '+@.DestName+'),SPD_nPubMask,SPD_nEditionMask,SPD_nGrid
ID,SPD_strPageID,SPD_strDescription,SPD_nSortNum,SPD_bIsVal
id from '+@.SourceName )
i want the subsqury (select isnull(max(AF_nFieldID),0)+1
from '+@.DestName+') make a new auto increase ID every
insert,but the sub query only make one ID,or any method
like oracle's sequence?thanksLook at identity in bol.
It will increment the field for every record inserted.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||thank you,i know the identity ,
but my question is that the destinatin table already have
data,and can't change the field to idenity
>--Original Message--
>Look at identity in bol.
>It will increment the field for every record inserted.
>Nigel Rivett
>www.nigelrivett.net
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>
about subscribers
i have millions of subscribers,
any good idea to manage those subscribers efficently?
thanks
Same old, same old... Query optimization (the most important thing!!!...).
And remember that you can add custom indexes to NS tables.
Also, if possible, break your application into several separate applications based on the module's functionality, and run them separately, maybe even on separate machines.
Also, for performance, use SQL Server server machine for the SQL stuff only, and move the NS service and all other resources-hungry stuff (custom event providers, content formatters, etc.) to other server(s).
Use subscription expiration to remove all "old" subscriptions, and once in a while prompt your customers to confirm their old subscriptions (that they still want to receive their alerts AND confirm email addresses, cell phone #s, etc.). Maybe, even distribute your own notification with the request for confirmation; do it a few times; if someone does not reply, remove this subscriber.
|||Thanks for that,and
Any good idea about how to create subscribers quickly,especially millions of subscribers
|||
For SSNS in 2000, you have to use the API.
In SSNS 2005, you can insert into subscriber views. Here's an example.
http://sqlns.blogspot.com/2005/10/creating-subscribers-in-2005.html
HTH...
Joe
About storing and receiving a location from SQL SERVER.....................
hi friends,
i want to store an image in DB. but most of my friends told that, to store an image in web server then store tat location in a DB and access it anywhere. this is for asp.net in C#-code behind. how to do this? i've a table with a text field. then ......? waiting for ur reply.........
note: i need coding. not by using controls. pls...
Hi,
if you want store the image on DB, you can pass it asSqlDbType.Image, and assign a byte array to the field to be saved.
cmd.Parameters.Add("@.Image",SqlDbType.Image, 0,"Image_Data");
DataRow row = new ...
row.Image_Data =File.ReadAllBytes(pathToFile); //assuming you uploaded it and saved it somewhere.
//your update command
Hope this help
About Stored Procedure
hi there,
I am using SQL after a long time and want to use stored procedure ofor the following purpose:
i have to db of all the names of agents all over US.i am using a formulata to calculate zip to zip distance so that when the user gets a order and hits"find agents" ..it calculates the distances by using latitude and longitude calculation and displays all the agents who are around 50 mile in radius with tthe zip code specified in the order.
I wanted to know if I could store this formula in a stored procedure so that all teh calculations are done by SQL and stored in a temp table..beucase the same thing i did in Access(obviously access doesnt have Stored proicedure and i wrote a program called calculate.asp to calculate diostances) its running slow and thats the only reason i am using SQL.
i dont know where to start from...any help/links/ideas on how to do it ,whether its possible to do it thru stored procedures...if so then how to write stored procedures?
hi,
yes, you can put that "solving" code in a stored procedure...
for instance, assuming you have "areas" indicated in an x,y cartesian table of 3x3 cells as the following trivial sample, you can get the related agent based on the join of the area and agents tables...
SET NOCOUNT ON;USE tempdb;
GO
CREATE TABLE dbo.Area (
Id int NOT NULL IDENTITY PRIMARY KEY,
Name varchar(10) NOT NULL,
X int NOT NULL,
Y int NOT NULL
);
CREATE TABLE dbo.Agent (
Id int NOT NULL IDENTITY PRIMARY KEY,
Name varchar(10) NOT NULL,
IdArea int NOT NULL
);
INSERT INTO dbo.Area VALUES ( 'a', 1 , 1 );
INSERT INTO dbo.Area VALUES ( 'b', 2 , 1 );
INSERT INTO dbo.Area VALUES ( 'c', 3 , 1 );
INSERT INTO dbo.Area VALUES ( 'd', 1 , 2 );
INSERT INTO dbo.Area VALUES ( 'e', 2 , 2 );
INSERT INTO dbo.Area VALUES ( 'f', 3 , 2 );
INSERT INTO dbo.Area VALUES ( 'g', 1 , 3 );
INSERT INTO dbo.Area VALUES ( 'h', 2 , 3 );
INSERT INTO dbo.Area VALUES ( 'i', 3 , 3 );
INSERT INTO dbo.Agent VALUES ( 'agent a', 1 );
INSERT INTO dbo.Agent VALUES ( 'agent b', 2 );
INSERT INTO dbo.Agent VALUES ( 'agent c', 3 );
INSERT INTO dbo.Agent VALUES ( 'agent d', 4 );
INSERT INTO dbo.Agent VALUES ( 'agent e', 5 );
INSERT INTO dbo.Agent VALUES ( 'agent f', 6 );
INSERT INTO dbo.Agent VALUES ( 'agent g', 7 );
INSERT INTO dbo.Agent VALUES ( 'agent h', 8 );
INSERT INTO dbo.Agent VALUES ( 'agent i', 9 );
DECLARE @.x int, @.y int;
SELECT @.x = 3, @.y = 1
SELECT a.Name AS [AreaName], g.Name AS [AgentName]
FROM dbo.Area a
JOIN dbo.Agent g
ON g.IdArea = a.Id
WHERE a.X = @.x AND a.Y = @.y
PRINT 'all agents and related areas';
SELECT a.Name AS [AreaName], g.Name AS [AgentName]
FROM dbo.Area a
JOIN dbo.Agent g
ON g.IdArea = a.Id;
DROP TABLE dbo.Agent, dbo.Area;
--<
AreaName AgentName
- -
c agent c
all agents and related areas
AreaName AgentName
- -
a agent a
b agent b
c agent c
d agent d
e agent e
f agent f
g agent g
h agent h
i agent i
the procedure related code could be as easy as the following related trivial code
CREATE PROCEDURE dbo.GetAgentByArea (@.x int,
@.y int
)
AS BEGIN
SELECT a.Name AS [AreaName], g.Name AS [AgentName]
FROM dbo.Area a
JOIN dbo.Agent g
ON g.IdArea = a.Id
WHERE a.X = @.x AND a.Y = @.y;
END;
GO
EXEC dbo.GetAgentByArea 1,1;
EXEC dbo.GetAgentByArea 3,2;
GO
DROP PROCEDURE dbo.GetAgentByArea
or as complicated as necessary..
regards
About stored proc compilation...
Does SQL Server compile stored procs to bytecode or to native x86 code? Is
there documentation on this somewhere?
Thanks,
JayNeither. It compiles them to an internal query plan that is executed by the
SQL engine. I do not know of any public documentation on the internal plan
structure. BOL discusses this in very general terms under the topic
'Execution Plan Caching and Reuse'.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
> Does SQL Server compile stored procs to bytecode or to native x86 code? Is
> there documentation on this somewhere?
> Thanks,
> Jay|||I sure appreciate the quick response. This is very helpful. A search in Book
s
Online with "query plan compile" gave some additional info. Thanks again.
Jay
"Geoff N. Hiten" wrote:
> Neither. It compiles them to an internal query plan that is executed by t
he
> SQL engine. I do not know of any public documentation on the internal pla
n
> structure. BOL discusses this in very general terms under the topic
> 'Execution Plan Caching and Reuse'.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
> news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
>
>
there documentation on this somewhere?
Thanks,
JayNeither. It compiles them to an internal query plan that is executed by the
SQL engine. I do not know of any public documentation on the internal plan
structure. BOL discusses this in very general terms under the topic
'Execution Plan Caching and Reuse'.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
> Does SQL Server compile stored procs to bytecode or to native x86 code? Is
> there documentation on this somewhere?
> Thanks,
> Jay|||I sure appreciate the quick response. This is very helpful. A search in Book
s
Online with "query plan compile" gave some additional info. Thanks again.
Jay
"Geoff N. Hiten" wrote:
> Neither. It compiles them to an internal query plan that is executed by t
he
> SQL engine. I do not know of any public documentation on the internal pla
n
> structure. BOL discusses this in very general terms under the topic
> 'Execution Plan Caching and Reuse'.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
> news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
>
>
About stored proc compilation...
Does SQL Server compile stored procs to bytecode or to native x86 code? Is
there documentation on this somewhere?
Thanks,
Jay
Neither. It compiles them to an internal query plan that is executed by the
SQL engine. I do not know of any public documentation on the internal plan
structure. BOL discusses this in very general terms under the topic
'Execution Plan Caching and Reuse'.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
> Does SQL Server compile stored procs to bytecode or to native x86 code? Is
> there documentation on this somewhere?
> Thanks,
> Jay
|||I sure appreciate the quick response. This is very helpful. A search in Books
Online with "query plan compile" gave some additional info. Thanks again.
Jay
"Geoff N. Hiten" wrote:
> Neither. It compiles them to an internal query plan that is executed by the
> SQL engine. I do not know of any public documentation on the internal plan
> structure. BOL discusses this in very general terms under the topic
> 'Execution Plan Caching and Reuse'.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
> news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
>
>
there documentation on this somewhere?
Thanks,
Jay
Neither. It compiles them to an internal query plan that is executed by the
SQL engine. I do not know of any public documentation on the internal plan
structure. BOL discusses this in very general terms under the topic
'Execution Plan Caching and Reuse'.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
> Does SQL Server compile stored procs to bytecode or to native x86 code? Is
> there documentation on this somewhere?
> Thanks,
> Jay
|||I sure appreciate the quick response. This is very helpful. A search in Books
Online with "query plan compile" gave some additional info. Thanks again.
Jay
"Geoff N. Hiten" wrote:
> Neither. It compiles them to an internal query plan that is executed by the
> SQL engine. I do not know of any public documentation on the internal plan
> structure. BOL discusses this in very general terms under the topic
> 'Execution Plan Caching and Reuse'.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jay Williams" <JayWilliams@.discussions.microsoft.com> wrote in message
> news:E9C9E85C-7CE2-4056-ABE3-E75CD3B515F0@.microsoft.com...
>
>
About Store Produre
Hi Every Body.
Its Biswajit I want to know about use of Store Procedure in
SQL.What is benifit of store procedure and we r using it."Biswajit Barik" <biswajitbarik@.rediffmail.com> wrote in message
news:311aa9ac.0404030003.4ac2d5ad@.posting.google.c om...
> Hi Every Body.
> Its Biswajit I want to know about use of Store Procedure in
> SQL.What is benifit of store procedure and we r using it.
Its Biswajit I want to know about use of Store Procedure in
SQL.What is benifit of store procedure and we r using it."Biswajit Barik" <biswajitbarik@.rediffmail.com> wrote in message
news:311aa9ac.0404030003.4ac2d5ad@.posting.google.c om...
> Hi Every Body.
> Its Biswajit I want to know about use of Store Procedure in
> SQL.What is benifit of store procedure and we r using it.
http://www.sommarskog.se/dynamic_sql.html#Why_SP
Also see "Stored Procedures" in Books Online.
Simon
about Store Procedure
Hi i have store procedure called runontime suppose... i want to run that
store procedure mid night from monday to friday automatically . is their any
facility available in sql server 2005 to do that like timer thanksCreate job? Have you check it out?
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
> Hi i have store procedure called runontime suppose... i want to run that
> store procedure mid night from monday to friday automatically . is their
> any
> facility available in sql server 2005 to do that like timer thanks|||Use the SQL Server agent - that's what it's there for! :)
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
> Hi i have store procedure called runontime suppose... i want to run that
> store procedure mid night from monday to friday automatically . is their
> any
> facility available in sql server 2005 to do that like timer thanks|||hi can u direct me to any artical or help about it thanks
"Uri Dimant" wrote:
> Create job? Have you check it out?
>
>
> "amjad" <amjad@.discussions.microsoft.com> wrote in message
> news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
>
>|||Ok, open BOL and put it in URL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/079c2984-0052-4a37-a2b8-4ece
56e6b6b5.htm
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:CF8B8E49-E0E0-4585-BD03-72D423092AA1@.microsoft.com...
> hi can u direct me to any artical or help about it thanks
> "Uri Dimant" wrote:
>
store procedure mid night from monday to friday automatically . is their any
facility available in sql server 2005 to do that like timer thanksCreate job? Have you check it out?
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
> Hi i have store procedure called runontime suppose... i want to run that
> store procedure mid night from monday to friday automatically . is their
> any
> facility available in sql server 2005 to do that like timer thanks|||Use the SQL Server agent - that's what it's there for! :)
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
> Hi i have store procedure called runontime suppose... i want to run that
> store procedure mid night from monday to friday automatically . is their
> any
> facility available in sql server 2005 to do that like timer thanks|||hi can u direct me to any artical or help about it thanks
"Uri Dimant" wrote:
> Create job? Have you check it out?
>
>
> "amjad" <amjad@.discussions.microsoft.com> wrote in message
> news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
>
>|||Ok, open BOL and put it in URL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/079c2984-0052-4a37-a2b8-4ece
56e6b6b5.htm
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:CF8B8E49-E0E0-4585-BD03-72D423092AA1@.microsoft.com...
> hi can u direct me to any artical or help about it thanks
> "Uri Dimant" wrote:
>
about store procedure
i have write several store procedure for export data,
most of the code such as :insert into Table1(col1,col2)
from select (col1,col2) form Table2,and i execute it in
sql analyser,and the analyser will show "x rows affected"
and now ,i want write a app with C#,call these store
procedures ,and want to konw
1,how many rows affected,analyser can return this?
2.how many rows selected,must i rewrite the store
procedure? such as add a new select count(*) from table2?
but this will decrease the perfermance
thank youCheck out SET NOCOUNT ON/OFF and @.@.ROWCOUNT in SQL Server Books Online.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you|||Frank..
One way some people do this is by returning a return status... Normally a
return status of 0 means the stored procedure was successfull, and a
negative return status means an error... Some people use a positive return
status to indicate how many rows were affected /select by the sp...
After the insert/select etc capture @.@.rowcount into a local variable..
declare @.error int, @.rowcount int
set nocount on
update ....
select @.rowcount = @.@.rowcount, @.error = @.@.error --I always capture
errors also
return @.rowcount
I always Set nocount ON as the first executable statement in an sp as well..
To use the return status
declare @.ret_status int
exec @.ret_status = myproc
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you
most of the code such as :insert into Table1(col1,col2)
from select (col1,col2) form Table2,and i execute it in
sql analyser,and the analyser will show "x rows affected"
and now ,i want write a app with C#,call these store
procedures ,and want to konw
1,how many rows affected,analyser can return this?
2.how many rows selected,must i rewrite the store
procedure? such as add a new select count(*) from table2?
but this will decrease the perfermance
thank youCheck out SET NOCOUNT ON/OFF and @.@.ROWCOUNT in SQL Server Books Online.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you|||Frank..
One way some people do this is by returning a return status... Normally a
return status of 0 means the stored procedure was successfull, and a
negative return status means an error... Some people use a positive return
status to indicate how many rows were affected /select by the sp...
After the insert/select etc capture @.@.rowcount into a local variable..
declare @.error int, @.rowcount int
set nocount on
update ....
select @.rowcount = @.@.rowcount, @.error = @.@.error --I always capture
errors also
return @.rowcount
I always Set nocount ON as the first executable statement in an sp as well..
To use the return status
declare @.ret_status int
exec @.ret_status = myproc
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you
about store proc
Hi i have store proc where i am excuting a query look like
Select A.* From A where a.Col1=0 Or A.Col2='ABC'
in this query the only problem is if A.Col1 result is null then it give me
error message that timeout expired. the timeout period elapsed prior to
completion of the operation or the server is not responding ....
i dont know why its behaving like that while with And operator its not
giving that problem ... any body has any idea what it is about thankstry thi instead...
Select A.* From A where a.Col1=0
union
Select A.* From A where A.Col2='ABC'|||Amjad
Be careful using UNION clause ,because it removes a "duplicate " rows
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:2C1E6EF7-4479-4E2E-97AB-C4F406469010@.microsoft.com...
> try thi instead...
> Select A.* From A where a.Col1=0
> union
> Select A.* From A where A.Col2='ABC'
>|||Hi Uri,
I suggested UNION because he was using an OR, so anyways there wouldn't
have been any duplicates, unless the table didn't have a primary key.
Anyways, thanks for the disclaimer, I should have given it :)|||Uri Dimant (urid@.iscar.co.il) writes:
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:2C1E6EF7-4479-4E2E-97AB-C4F406469010@.microsoft.com...
> Amjad
> Be careful using UNION clause ,because it removes a "duplicate " rows
So does OR.
Then again, given the WHERE condition, there cannot be any duplicates, so
UNION ALL is a good idea.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> Uri Dimant (urid@.iscar.co.il) writes:
> So does OR.
> Then again, given the WHERE condition, there cannot be any duplicates, so
> UNION ALL is a good idea.
Eh, I didn't read the query well enough, but thought it was the same column
in both cases.
Given that, you should use UNION and not UNION ALL, if you try this
workaround.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||examnotes (amjad@.discussions.microsoft.com) writes:
> Hi i have store proc where i am excuting a query look like
>
> Select A.* From A where a.Col1=0 Or A.Col2='ABC'
> in this query the only problem is if A.Col1 result is null then it give me
> error message that timeout expired. the timeout period elapsed prior to
> completion of the operation or the server is not responding ....
> i dont know why its behaving like that while with And operator its not
> giving that problem ... any body has any idea what it is about thanks
There is a very big difference between AND and OR.
Are both columns indexed? And is the distribution in the column selective
enough?
Say that there is only a column on Col2 and you run:
Select A.* From A where a.Col1=0 AND A.Col2='ABC'
SQL Server can find the rows by using the index on Col2, and then check Col1
to see if the row is to be included.
But with OR, SQL Server must check all rows for Col1, which means that it
has to scan the table. Which can take a long time if the table is huge.
If both columns are indexed, SQL Server can use both indexes, but in such
case it needs to add an extra operator to the query plan to sort out the
duplicates.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi i got different result using union operator
like if i run Select A.* From A Where a.col1=0 give me 133 record and then
if i run Select A.* From A where a.col2='abc' it give me 186 but when i run
with union it gave me 169 records i dont know why thanks
"Omnibuzz" wrote:
> try thi instead...
> Select A.* From A where a.Col1=0
> union
> Select A.* From A where A.Col2='ABC'
>|||Thats because union removes the duplicates.
if you use UNION ALL, then you will get the total right.
But I guess you would want to eiliminate the duplicates|||thanks for all of you. its working now with Union All
"Omnibuzz" wrote:
> Thats because union removes the duplicates.
> if you use UNION ALL, then you will get the total right.
> But I guess you would want to eiliminate the duplicates
>
Select A.* From A where a.Col1=0 Or A.Col2='ABC'
in this query the only problem is if A.Col1 result is null then it give me
error message that timeout expired. the timeout period elapsed prior to
completion of the operation or the server is not responding ....
i dont know why its behaving like that while with And operator its not
giving that problem ... any body has any idea what it is about thankstry thi instead...
Select A.* From A where a.Col1=0
union
Select A.* From A where A.Col2='ABC'|||Amjad
Be careful using UNION clause ,because it removes a "duplicate " rows
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:2C1E6EF7-4479-4E2E-97AB-C4F406469010@.microsoft.com...
> try thi instead...
> Select A.* From A where a.Col1=0
> union
> Select A.* From A where A.Col2='ABC'
>|||Hi Uri,
I suggested UNION because he was using an OR, so anyways there wouldn't
have been any duplicates, unless the table didn't have a primary key.
Anyways, thanks for the disclaimer, I should have given it :)|||Uri Dimant (urid@.iscar.co.il) writes:
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:2C1E6EF7-4479-4E2E-97AB-C4F406469010@.microsoft.com...
> Amjad
> Be careful using UNION clause ,because it removes a "duplicate " rows
So does OR.
Then again, given the WHERE condition, there cannot be any duplicates, so
UNION ALL is a good idea.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> Uri Dimant (urid@.iscar.co.il) writes:
> So does OR.
> Then again, given the WHERE condition, there cannot be any duplicates, so
> UNION ALL is a good idea.
Eh, I didn't read the query well enough, but thought it was the same column
in both cases.
Given that, you should use UNION and not UNION ALL, if you try this
workaround.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||examnotes (amjad@.discussions.microsoft.com) writes:
> Hi i have store proc where i am excuting a query look like
>
> Select A.* From A where a.Col1=0 Or A.Col2='ABC'
> in this query the only problem is if A.Col1 result is null then it give me
> error message that timeout expired. the timeout period elapsed prior to
> completion of the operation or the server is not responding ....
> i dont know why its behaving like that while with And operator its not
> giving that problem ... any body has any idea what it is about thanks
There is a very big difference between AND and OR.
Are both columns indexed? And is the distribution in the column selective
enough?
Say that there is only a column on Col2 and you run:
Select A.* From A where a.Col1=0 AND A.Col2='ABC'
SQL Server can find the rows by using the index on Col2, and then check Col1
to see if the row is to be included.
But with OR, SQL Server must check all rows for Col1, which means that it
has to scan the table. Which can take a long time if the table is huge.
If both columns are indexed, SQL Server can use both indexes, but in such
case it needs to add an extra operator to the query plan to sort out the
duplicates.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi i got different result using union operator
like if i run Select A.* From A Where a.col1=0 give me 133 record and then
if i run Select A.* From A where a.col2='abc' it give me 186 but when i run
with union it gave me 169 records i dont know why thanks
"Omnibuzz" wrote:
> try thi instead...
> Select A.* From A where a.Col1=0
> union
> Select A.* From A where A.Col2='ABC'
>|||Thats because union removes the duplicates.
if you use UNION ALL, then you will get the total right.
But I guess you would want to eiliminate the duplicates|||thanks for all of you. its working now with Union All
"Omnibuzz" wrote:
> Thats because union removes the duplicates.
> if you use UNION ALL, then you will get the total right.
> But I guess you would want to eiliminate the duplicates
>
about SqlTransaction
updatestring = "Update [Flow] set received=1 where flowid=@.flowid"
insertstring = "Insert into [Flow] (barcode, copyid, floworder, recvdate, recvuser, recvdepartment) "
insertstring += "values (@.barcode, @.copyid, @.floworder, @.recvdate, @.recvuser, @.recvdepartment)"
insertstring = "Insert into [Flow] (barcode, copyid, floworder, recvdate, recvuser, recvdepartment) "
insertstring += "values (@.barcode, @.copyid, @.floworder, @.recvdate, @.recvuser, @.recvdepartment)"
is it can't use sqltransaction in the same table ?
when i not apply the sqltransaction, then the about two queries can work success, but when i apply the sqltransaction, then have an error in following.
error :
Object reference not set to an instance of an object
Source Error:
Line 278: message.Text = "Recebido o documento " & barcodetx.Text & "!!"
Line 279: Catch ex As Exception
<red>Line 280: objtransaction.Rollback()</red>
Line 281: message.Text = "Não se conseguiu registar!!"
Line 282: FinallyHi
You haven't posted any of the real code where stuff is happening.
You are probably best of wrapping the whole thing inside a stored proc and calling that.
About sqlserver2000 error in application log
Hi !
i have a SQL 2000 A/P cluster with 4 CPU ancd 8 GByte with AWE enabled; my
db as work fine but from same day a error was dispaly in my application log
:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 28/07/2006
Time: 9.32.02
User: N/A
Computer: CLSSQL2000
Description:
Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration
I have check for memory free with taskmanager and there is 2 Gb yte frre (i
have awe with 6 Gbyte configured) and with spconfigure but the awe and
memory configuration seems ok.
But why this error occurred ? What happened ?
Thanks in advance!is there any long running store procedure / query running on that server
with following statement.
Select *... INTO <table1> FROM ...
When queries like that runs, it applies exclusive lock on the system tables.
you would get "SQL Server cannot obtain a LOCK resource at this time" error
on the database until the above sp/query is completed. you may get the same
error when you try to list the table in enterprise manager.
change the code by creating the table first and do the insert into, instead
of Select ... into ...
Create <table> ( field1...,
field2...)
Insert into <table> SELECT ... FROM ...
-Saravanan
<io.com@.newsgroup.nospam> wrote in message
news:ezy5pGWxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi !
> i have a SQL 2000 A/P cluster with 4 CPU ancd 8 GByte with AWE enabled; my
> db as work fine but from same day a error was dispaly in my application
> log :
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 28/07/2006
> Time: 9.32.02
> User: N/A
> Computer: CLSSQL2000
> Description:
> Error: 1204, Severity: 19, State: 1
> The SQL Server cannot obtain a LOCK resource at this time. Rerun your
> statement when there are fewer active users or ask the system
> administrator to check the SQL Server lock and memory configuration
>
> I have check for memory free with taskmanager and there is 2 Gb yte frre
> (i have awe with 6 Gbyte configured) and with spconfigure but the awe and
> memory configuration seems ok.
> But why this error occurred ? What happened ?
> Thanks in advance!
>|||Hi,
Thanks Saravanan for greate suggestion. You can also refer to following
article:
323630 How to resolve blocking problems that are caused by lock escalation
in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;323630
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================
Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: <io.com@.newsgroup.nospam>
>>Subject: About sqlserver2000 error in application log
>>Date: Mon, 21 Aug 2006 22:37:42 +0200
>>Lines: 30
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
>>Message-ID: <ezy5pGWxGHA.5056@.TK2MSFTNGP06.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 62.241.4.149
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP06.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:442635
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Hi !
>>i have a SQL 2000 A/P cluster with 4 CPU ancd 8 GByte with AWE enabled;
my
>>db as work fine but from same day a error was dispaly in my application
log
>>:
>>Event Type: Error
>>Event Source: MSSQLSERVER
>>Event Category: (2)
>>Event ID: 17052
>>Date: 28/07/2006
>>Time: 9.32.02
>>User: N/A
>>Computer: CLSSQL2000
>>Description:
>>Error: 1204, Severity: 19, State: 1
>>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>>statement when there are fewer active users or ask the system
administrator
>>to check the SQL Server lock and memory configuration
>>
>>I have check for memory free with taskmanager and there is 2 Gb yte frre
(i
>>have awe with 6 Gbyte configured) and with spconfigure but the awe and
>>memory configuration seems ok.
>>But why this error occurred ? What happened ?
>>Thanks in advance!
>>
i have a SQL 2000 A/P cluster with 4 CPU ancd 8 GByte with AWE enabled; my
db as work fine but from same day a error was dispaly in my application log
:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 28/07/2006
Time: 9.32.02
User: N/A
Computer: CLSSQL2000
Description:
Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration
I have check for memory free with taskmanager and there is 2 Gb yte frre (i
have awe with 6 Gbyte configured) and with spconfigure but the awe and
memory configuration seems ok.
But why this error occurred ? What happened ?
Thanks in advance!is there any long running store procedure / query running on that server
with following statement.
Select *... INTO <table1> FROM ...
When queries like that runs, it applies exclusive lock on the system tables.
you would get "SQL Server cannot obtain a LOCK resource at this time" error
on the database until the above sp/query is completed. you may get the same
error when you try to list the table in enterprise manager.
change the code by creating the table first and do the insert into, instead
of Select ... into ...
Create <table> ( field1...,
field2...)
Insert into <table> SELECT ... FROM ...
-Saravanan
<io.com@.newsgroup.nospam> wrote in message
news:ezy5pGWxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi !
> i have a SQL 2000 A/P cluster with 4 CPU ancd 8 GByte with AWE enabled; my
> db as work fine but from same day a error was dispaly in my application
> log :
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 28/07/2006
> Time: 9.32.02
> User: N/A
> Computer: CLSSQL2000
> Description:
> Error: 1204, Severity: 19, State: 1
> The SQL Server cannot obtain a LOCK resource at this time. Rerun your
> statement when there are fewer active users or ask the system
> administrator to check the SQL Server lock and memory configuration
>
> I have check for memory free with taskmanager and there is 2 Gb yte frre
> (i have awe with 6 Gbyte configured) and with spconfigure but the awe and
> memory configuration seems ok.
> But why this error occurred ? What happened ?
> Thanks in advance!
>|||Hi,
Thanks Saravanan for greate suggestion. You can also refer to following
article:
323630 How to resolve blocking problems that are caused by lock escalation
in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;323630
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================
Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: <io.com@.newsgroup.nospam>
>>Subject: About sqlserver2000 error in application log
>>Date: Mon, 21 Aug 2006 22:37:42 +0200
>>Lines: 30
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
>>Message-ID: <ezy5pGWxGHA.5056@.TK2MSFTNGP06.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 62.241.4.149
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP06.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:442635
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Hi !
>>i have a SQL 2000 A/P cluster with 4 CPU ancd 8 GByte with AWE enabled;
my
>>db as work fine but from same day a error was dispaly in my application
log
>>:
>>Event Type: Error
>>Event Source: MSSQLSERVER
>>Event Category: (2)
>>Event ID: 17052
>>Date: 28/07/2006
>>Time: 9.32.02
>>User: N/A
>>Computer: CLSSQL2000
>>Description:
>>Error: 1204, Severity: 19, State: 1
>>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>>statement when there are fewer active users or ask the system
administrator
>>to check the SQL Server lock and memory configuration
>>
>>I have check for memory free with taskmanager and there is 2 Gb yte frre
(i
>>have awe with 6 Gbyte configured) and with spconfigure but the awe and
>>memory configuration seems ok.
>>But why this error occurred ? What happened ?
>>Thanks in advance!
>>
About sqlserver2000 error in application log
Hi !
i have a SQL 2000 A/P cluster with 4 CPU ancd 8 GByte with AWE enabled; my
db as work fine but from same day a error was dispaly in my application log
:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 28/07/2006
Time: 9.32.02
User: N/A
Computer: CLSSQL2000
Description:
Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration
I have check for memory free with taskmanager and there is 2 Gb yte frre (i
have awe with 6 Gbyte configured) and with spconfigure but the awe and
memory configuration seems ok.
But why this error occurred ? What happened ?
Thanks in advance!is there any long running store procedure / query running on that server
with following statement.
Select *... INTO <table1> FROM ...
When queries like that runs, it applies exclusive lock on the system tables.
you would get "SQL Server cannot obtain a LOCK resource at this time" error
on the database until the above sp/query is completed. you may get the same
error when you try to list the table in enterprise manager.
change the code by creating the table first and do the insert into, instead
of Select ... into ...
Create <table> ( field1...,
field2...)
Insert into <table> SELECT ... FROM ...
-Saravanan
<io.com@.newsgroup.nospam> wrote in message
news:ezy5pGWxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi !
> i have a SQL 2000 A/P cluster with 4 CPU ancd 8 GByte with AWE enabled; my
> db as work fine but from same day a error was dispaly in my application
> log :
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 28/07/2006
> Time: 9.32.02
> User: N/A
> Computer: CLSSQL2000
> Description:
> Error: 1204, Severity: 19, State: 1
> The SQL Server cannot obtain a LOCK resource at this time. Rerun your
> statement when there are fewer active users or ask the system
> administrator to check the SQL Server lock and memory configuration
>
> I have check for memory free with taskmanager and there is 2 Gb yte frre
> (i have awe with 6 Gbyte configured) and with spconfigure but the awe and
> memory configuration seems ok.
> But why this error occurred ? What happened ?
> Thanks in advance!
>|||Hi,
Thanks Saravanan for greate suggestion. You can also refer to following
article:
323630 How to resolve blocking problems that are caused by lock escalation
in SQL Server
http://support.microsoft.com/defaul...kb;EN-US;323630
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
my[vbcol=seagreen]
log[vbcol=seagreen]
administrator[vbcol=seagreen]
(i[vbcol=seagreen]
i have a SQL 2000 A/P cluster with 4 CPU ancd 8 GByte with AWE enabled; my
db as work fine but from same day a error was dispaly in my application log
:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 28/07/2006
Time: 9.32.02
User: N/A
Computer: CLSSQL2000
Description:
Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration
I have check for memory free with taskmanager and there is 2 Gb yte frre (i
have awe with 6 Gbyte configured) and with spconfigure but the awe and
memory configuration seems ok.
But why this error occurred ? What happened ?
Thanks in advance!is there any long running store procedure / query running on that server
with following statement.
Select *... INTO <table1> FROM ...
When queries like that runs, it applies exclusive lock on the system tables.
you would get "SQL Server cannot obtain a LOCK resource at this time" error
on the database until the above sp/query is completed. you may get the same
error when you try to list the table in enterprise manager.
change the code by creating the table first and do the insert into, instead
of Select ... into ...
Create <table> ( field1...,
field2...)
Insert into <table> SELECT ... FROM ...
-Saravanan
<io.com@.newsgroup.nospam> wrote in message
news:ezy5pGWxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi !
> i have a SQL 2000 A/P cluster with 4 CPU ancd 8 GByte with AWE enabled; my
> db as work fine but from same day a error was dispaly in my application
> log :
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 28/07/2006
> Time: 9.32.02
> User: N/A
> Computer: CLSSQL2000
> Description:
> Error: 1204, Severity: 19, State: 1
> The SQL Server cannot obtain a LOCK resource at this time. Rerun your
> statement when there are fewer active users or ask the system
> administrator to check the SQL Server lock and memory configuration
>
> I have check for memory free with taskmanager and there is 2 Gb yte frre
> (i have awe with 6 Gbyte configured) and with spconfigure but the awe and
> memory configuration seems ok.
> But why this error occurred ? What happened ?
> Thanks in advance!
>|||Hi,
Thanks Saravanan for greate suggestion. You can also refer to following
article:
323630 How to resolve blocking problems that are caused by lock escalation
in SQL Server
http://support.microsoft.com/defaul...kb;EN-US;323630
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
my[vbcol=seagreen]
log[vbcol=seagreen]
administrator[vbcol=seagreen]
(i[vbcol=seagreen]
About SQLH2 installation problem .....
hi,
I just tried to install SQL Health and Performance Tool (SQLH2), but when
set the schedule task procedure of the installation, it occured error. After
I read the guide, my server is Window 2000 server, it said it can only
support window server 2003 & window XP(I know that). And the wizard said
press "continue" to ignore that problem and need to set the schedule by
manual, but after I pressed "continue", it still occured that error and no
any other response of it.
So, anyone meet that error as like as me b4?
I feel appreciate anyone can help me ! ^^
Regards,
Tarek ^^'
Tarek's WorkShop
This is a bug in configuration utility.
At this point SQLH2 is installed and configured. You only need to add task
to run it on schedule. You have to do that manually on W2K.
Command to run (int the task) - sqlh2.exe
Thanks
Grigory
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"Tarek" <Tarek@.discussions.microsoft.com> wrote in message
news:86829E85-5171-46DD-9B1F-35522EB726AF@.microsoft.com...
> hi,
> I just tried to install SQL Health and Performance Tool (SQLH2), but when
> set the schedule task procedure of the installation, it occured error.
After
> I read the guide, my server is Window 2000 server, it said it can only
> support window server 2003 & window XP(I know that). And the wizard said
> press "continue" to ignore that problem and need to set the schedule by
> manual, but after I pressed "continue", it still occured that error and no
> any other response of it.
> So, anyone meet that error as like as me b4?
> I feel appreciate anyone can help me ! ^^
>
> --
> Regards,
> Tarek ^^'
> --
> Tarek's WorkShop
|||thanks Grigory, let me try it.
Tarek ^^
"Grigory Pogulsky [MS]" wrote:
> This is a bug in configuration utility.
> At this point SQLH2 is installed and configured. You only need to add task
> to run it on schedule. You have to do that manually on W2K.
> Command to run (int the task) - sqlh2.exe
> --
> Thanks
> Grigory
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Please reply to the newsgroups only, thanks.
>
> "Tarek" <Tarek@.discussions.microsoft.com> wrote in message
> news:86829E85-5171-46DD-9B1F-35522EB726AF@.microsoft.com...
> After
>
>
I just tried to install SQL Health and Performance Tool (SQLH2), but when
set the schedule task procedure of the installation, it occured error. After
I read the guide, my server is Window 2000 server, it said it can only
support window server 2003 & window XP(I know that). And the wizard said
press "continue" to ignore that problem and need to set the schedule by
manual, but after I pressed "continue", it still occured that error and no
any other response of it.
So, anyone meet that error as like as me b4?
I feel appreciate anyone can help me ! ^^
Regards,
Tarek ^^'
Tarek's WorkShop
This is a bug in configuration utility.
At this point SQLH2 is installed and configured. You only need to add task
to run it on schedule. You have to do that manually on W2K.
Command to run (int the task) - sqlh2.exe
Thanks
Grigory
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"Tarek" <Tarek@.discussions.microsoft.com> wrote in message
news:86829E85-5171-46DD-9B1F-35522EB726AF@.microsoft.com...
> hi,
> I just tried to install SQL Health and Performance Tool (SQLH2), but when
> set the schedule task procedure of the installation, it occured error.
After
> I read the guide, my server is Window 2000 server, it said it can only
> support window server 2003 & window XP(I know that). And the wizard said
> press "continue" to ignore that problem and need to set the schedule by
> manual, but after I pressed "continue", it still occured that error and no
> any other response of it.
> So, anyone meet that error as like as me b4?
> I feel appreciate anyone can help me ! ^^
>
> --
> Regards,
> Tarek ^^'
> --
> Tarek's WorkShop
|||thanks Grigory, let me try it.
Tarek ^^
"Grigory Pogulsky [MS]" wrote:
> This is a bug in configuration utility.
> At this point SQLH2 is installed and configured. You only need to add task
> to run it on schedule. You have to do that manually on W2K.
> Command to run (int the task) - sqlh2.exe
> --
> Thanks
> Grigory
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Please reply to the newsgroups only, thanks.
>
> "Tarek" <Tarek@.discussions.microsoft.com> wrote in message
> news:86829E85-5171-46DD-9B1F-35522EB726AF@.microsoft.com...
> After
>
>
About SQLClient conters
Hi,
how can i get the following list of counters
in runtime when we executing the application with
database support and is there any command to find the
no.of connection are made in the database and no.of pooled
connection, no.of non pooled connection for the database.> Hi,
> how can i get the following list of counters
> in runtime when we executing the application with
> database support and is there any command to find the
> no.of connection are made in the database and no.of pooled
> connection, no.of non pooled connection for the database.
>
--
You should look at incorporating Windows Management Instrumentation (WMI)
into your application. This is a place to start:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/
wmi_start_page.asp
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
how can i get the following list of counters
in runtime when we executing the application with
database support and is there any command to find the
no.of connection are made in the database and no.of pooled
connection, no.of non pooled connection for the database.> Hi,
> how can i get the following list of counters
> in runtime when we executing the application with
> database support and is there any command to find the
> no.of connection are made in the database and no.of pooled
> connection, no.of non pooled connection for the database.
>
--
You should look at incorporating Windows Management Instrumentation (WMI)
into your application. This is a place to start:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/
wmi_start_page.asp
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
About Sql25K and MDAC libraries
Dear all,
There is a problem which I am concerned about and is that kind of
compatibility own the old libraries, i.e, ado 2.5,2.6.,2.7sp1 within a Sql
Server 2005 framework.
We have a lot of old applications running with 2.6 and using RDO or even
DAO!!!
Does anyone have or is awared of any link related, any map or schema very
representative of all this stuff? Is very tricky try to imagine how manage
myself with such diversity of applications
Thanks in advance and regards,
Enric wrote on Wed, 22 Mar 2006 00:15:27 -0800:
> Dear all,
> There is a problem which I am concerned about and is that kind of
> compatibility own the old libraries, i.e, ado 2.5,2.6.,2.7sp1 within a Sql
> Server 2005 framework.
> We have a lot of old applications running with 2.6 and using RDO or even
> DAO!!!
> Does anyone have or is awared of any link related, any map or schema very
> representative of all this stuff? Is very tricky try to imagine how manage
> myself with such diversity of applications
> Thanks in advance and regards,
I have some VB apps using ADO 2.1 via ODBC that work fine with SQL Server
2005. ADO is an abstraction layer, not a driver library. SQL Server 2005
still supports the older connection methods.
Dan
There is a problem which I am concerned about and is that kind of
compatibility own the old libraries, i.e, ado 2.5,2.6.,2.7sp1 within a Sql
Server 2005 framework.
We have a lot of old applications running with 2.6 and using RDO or even
DAO!!!
Does anyone have or is awared of any link related, any map or schema very
representative of all this stuff? Is very tricky try to imagine how manage
myself with such diversity of applications
Thanks in advance and regards,
Enric wrote on Wed, 22 Mar 2006 00:15:27 -0800:
> Dear all,
> There is a problem which I am concerned about and is that kind of
> compatibility own the old libraries, i.e, ado 2.5,2.6.,2.7sp1 within a Sql
> Server 2005 framework.
> We have a lot of old applications running with 2.6 and using RDO or even
> DAO!!!
> Does anyone have or is awared of any link related, any map or schema very
> representative of all this stuff? Is very tricky try to imagine how manage
> myself with such diversity of applications
> Thanks in advance and regards,
I have some VB apps using ADO 2.1 via ODBC that work fine with SQL Server
2005. ADO is an abstraction layer, not a driver library. SQL Server 2005
still supports the older connection methods.
Dan
About Sql25K and MDAC libraries
Dear all,
There is a problem which I am concerned about and is that kind of
compatibility own the old libraries, i.e, ado 2.5,2.6.,2.7sp1 within a Sql
Server 2005 framework.
We have a lot of old applications running with 2.6 and using RDO or even
DAO!!!
Does anyone have or is awared of any link related, any map or schema very
representative of all this stuff? Is very tricky try to imagine how manage
myself with such diversity of applications
Thanks in advance and regards,Enric wrote on Wed, 22 Mar 2006 00:15:27 -0800:
> Dear all,
> There is a problem which I am concerned about and is that kind of
> compatibility own the old libraries, i.e, ado 2.5,2.6.,2.7sp1 within a Sql
> Server 2005 framework.
> We have a lot of old applications running with 2.6 and using RDO or even
> DAO!!!
> Does anyone have or is awared of any link related, any map or schema very
> representative of all this stuff? Is very tricky try to imagine how manage
> myself with such diversity of applications
> Thanks in advance and regards,
I have some VB apps using ADO 2.1 via ODBC that work fine with SQL Server
2005. ADO is an abstraction layer, not a driver library. SQL Server 2005
still supports the older connection methods.
Dan
There is a problem which I am concerned about and is that kind of
compatibility own the old libraries, i.e, ado 2.5,2.6.,2.7sp1 within a Sql
Server 2005 framework.
We have a lot of old applications running with 2.6 and using RDO or even
DAO!!!
Does anyone have or is awared of any link related, any map or schema very
representative of all this stuff? Is very tricky try to imagine how manage
myself with such diversity of applications
Thanks in advance and regards,Enric wrote on Wed, 22 Mar 2006 00:15:27 -0800:
> Dear all,
> There is a problem which I am concerned about and is that kind of
> compatibility own the old libraries, i.e, ado 2.5,2.6.,2.7sp1 within a Sql
> Server 2005 framework.
> We have a lot of old applications running with 2.6 and using RDO or even
> DAO!!!
> Does anyone have or is awared of any link related, any map or schema very
> representative of all this stuff? Is very tricky try to imagine how manage
> myself with such diversity of applications
> Thanks in advance and regards,
I have some VB apps using ADO 2.1 via ODBC that work fine with SQL Server
2005. ADO is an abstraction layer, not a driver library. SQL Server 2005
still supports the older connection methods.
Dan
About Sql25K and MDAC libraries
Dear all,
There is a problem which I am concerned about and is that kind of
compatibility own the old libraries, i.e, ado 2.5,2.6.,2.7sp1 within a Sql
Server 2005 framework.
We have a lot of old applications running with 2.6 and using RDO or even
DAO!!!
Does anyone have or is awared of any link related, any map or schema very
representative of all this stuff? Is very tricky try to imagine how manage
myself with such diversity of applications
Thanks in advance and regards,Enric wrote on Wed, 22 Mar 2006 00:15:27 -0800:
> Dear all,
> There is a problem which I am concerned about and is that kind of
> compatibility own the old libraries, i.e, ado 2.5,2.6.,2.7sp1 within a Sql
> Server 2005 framework.
> We have a lot of old applications running with 2.6 and using RDO or even
> DAO!!!
> Does anyone have or is awared of any link related, any map or schema very
> representative of all this stuff? Is very tricky try to imagine how manage
> myself with such diversity of applications
> Thanks in advance and regards,
I have some VB apps using ADO 2.1 via ODBC that work fine with SQL Server
2005. ADO is an abstraction layer, not a driver library. SQL Server 2005
still supports the older connection methods.
Dan
There is a problem which I am concerned about and is that kind of
compatibility own the old libraries, i.e, ado 2.5,2.6.,2.7sp1 within a Sql
Server 2005 framework.
We have a lot of old applications running with 2.6 and using RDO or even
DAO!!!
Does anyone have or is awared of any link related, any map or schema very
representative of all this stuff? Is very tricky try to imagine how manage
myself with such diversity of applications
Thanks in advance and regards,Enric wrote on Wed, 22 Mar 2006 00:15:27 -0800:
> Dear all,
> There is a problem which I am concerned about and is that kind of
> compatibility own the old libraries, i.e, ado 2.5,2.6.,2.7sp1 within a Sql
> Server 2005 framework.
> We have a lot of old applications running with 2.6 and using RDO or even
> DAO!!!
> Does anyone have or is awared of any link related, any map or schema very
> representative of all this stuff? Is very tricky try to imagine how manage
> myself with such diversity of applications
> Thanks in advance and regards,
I have some VB apps using ADO 2.1 via ODBC that work fine with SQL Server
2005. ADO is an abstraction layer, not a driver library. SQL Server 2005
still supports the older connection methods.
Dan
About SQL2000 SP4 and DTC e Linked Server
Hi,
the scenario is :
- cluster windows 2003 SP1 withc SQL2000 SP4 A/P
- DTC on both server was configured with the modified after the service
pack1
In SQL 2000 i have 2 database:
1- DB1
2- DB2
For the DB2 i have created a linked server and it seems work .
Now i would start a distribuited transaction across DB1 & DB2 , the
stantment SQL is like this :
begin tran
insert into table-1
select top 100 convert(varchar(100),idMov) as Chiav , num from
LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
commit tran
if i run it a error was show :
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I check for MSDTC configuration but it seems right.
I check for this error and i found some KB that indicated a isuee with
MSDTC, so i test it with the DTCTester but the tool tell work all fine !
ARGH!!!!
It's important see that the transaction start on the node1 use a "local"
MSDTC contat a "local" linked server that poit to "local" sql server
instance for use a "local" DB2.
The MSDTC is cluster configuration.
There is any problem to use a ditribuited transaction with a "local" linked
server ?
Anybody have any idea ?
Thanks in advance.
Hi,
You don't mention which KB's you've looked at, but try to see if the 2
links below helps you any further?
http://support.microsoft.com/kb/301600/
http://support.microsoft.com/kb/817064/
Regards
Steen Schlter Persson
Database Administrator / System Administrator
alterx@.noemail.noemail wrote:
> Hi,
> the scenario is :
> - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
> - DTC on both server was configured with the modified after the service
> pack1
>
> In SQL 2000 i have 2 database:
> 1- DB1
> 2- DB2
> For the DB2 i have created a linked server and it seems work .
> Now i would start a distribuited transaction across DB1 & DB2 , the
> stantment SQL is like this :
>
> begin tran
> insert into table-1
> select top 100 convert(varchar(100),idMov) as Chiav , num from
> LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
> commit tran
>
> if i run it a error was show :
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> I check for MSDTC configuration but it seems right.
> I check for this error and i found some KB that indicated a isuee with
> MSDTC, so i test it with the DTCTester but the tool tell work all fine !
> ARGH!!!!
> It's important see that the transaction start on the node1 use a "local"
> MSDTC contat a "local" linked server that poit to "local" sql server
> instance for use a "local" DB2.
> The MSDTC is cluster configuration.
> There is any problem to use a ditribuited transaction with a "local"
> linked server ?
>
> Anybody have any idea ?
> Thanks in advance.
>
|||Hello,
Thank you for posting here.
From the problem description of the post you submitted, my understanding
is: when you attempt to start a Distributed Transaction across DB 1 and DB
2, the following error message is received:
########################
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction. [OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
########################
In addition, you have created a linked server for DB2, which seems work
properly. If I have misunderstood about your concern, feel free to let me
know.
Based on my experience, the following error message can normally be caused
by one of the following
1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
network transactions.
2. Windows Firewall is enabled on the computer. By default, Windows
Firewall blocks the MSDTC program.
3. Communication issue between DB1 and DB2
Note This problem may occur even when Windows Firewall is turned off.
In your post, you have indicated that the MSDTC has been verified by the
DTCTester tool but this tool tell work fine.
First of all, I would like to check whether the object on the destination
server (DB2) refers back to the first server (DB1). This is what is known
as a loopback situation. This is not supported, as documented in SQL Server
Books Online. For more information, visit the following Microsoft Web site:
Loopback Linked Servers
http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
Next, lets manually verify your network name resolution works. Verify
that the servers can communicate with one another by name, not just by IP
address. Check in both directions (for example, from DB1 to DB2 and from
DB2 to DB1). You must resolve all name resolution problems on the network
before you run your distributed query. This may involve updating WINS, DNS,
or LMHost files.
If Windows Firewall or other third party firewall is used, please also make
sure that your Remote Procedure Call (RPC) ports are opened correctly.
Below are the steps to configure Windows Firewall to include the MSDTC
program and to include port 135 as an exception. To do this, follow these
steps:
==============================
a. Click Start, and then click Run.
b. In the Run dialog box, type Firewall.cpl , and then click OK
c. In Control Panel, double-click Windows Firewall.
d. In the Windows Firewall dialog box, click Add Program on the Exceptions
tab.
e. In the Add a Program dialog box, click the Browse button, and then
locate the Msdtc.exe file. By default, the file is stored in the
<Installation drive>:\Windows\System32 folder.
f. In the Add a Program dialog box, click OK.
g. In the Windows Firewall dialog box, click to select the msdtc option in
the Programs and Services list.
h. Click Add Port on the Exceptions tab.
i. In the Add a Port dialog box, type 135 in the Port number text box, and
then click to select the TCP option.
j. In the Add a Port dialog box, type a name for the exception in the Name
text box, and then click OK.
k. In the Windows Firewall dialog box, select the name that you used for
the exception in step j in the Programs and Services list, and then click
OK.
In addition, although we have used the DTCTester tool to manually verify
the MSDTC service, it is also recommended that we manually make sure that
the Log On As account for the MSDTC service is the Network Service account
and it allow the network transaction.
For the detail steps, please refer to the STEP ONE and STEP TWO in the
following KB article
You receive error 7391 when you run a distributed transaction against a
linked server in SQL Server 2000 on a computer that is running Windows
Server 2003
http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
If anything is unclear in my post, please don't hesitate to let me know and
I will be glad to help. Thank you for your efforts and time.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: <alterx@.noemail.noemail>
| Subject: About SQL2000 SP4 and DTC e Linked Server
| Date: Wed, 13 Jun 2007 20:50:41 +0200
| Lines: 60
| Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=original
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| X-MS-CommunityGroup-MessageCategory:
{E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi,
|
| the scenario is :
|
| - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| - DTC on both server was configured with the modified after the service
| pack1
|
|
| In SQL 2000 i have 2 database:
|
| 1- DB1
| 2- DB2
|
| For the DB2 i have created a linked server and it seems work .
|
| Now i would start a distribuited transaction across DB1 & DB2 , the
| stantment SQL is like this :
|
|
| begin tran
| insert into table-1
| select top 100 convert(varchar(100),idMov) as Chiav , num from
| LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| commit tran
|
|
|
| if i run it a error was show :
|
| Server: Msg 7391, Level 16, State 1, Line 2
| The operation could not be performed because the OLE DB provider
'SQLOLEDB'
| was unable to begin a distributed transaction.
| [OLE/DB provider returned message: New transaction cannot enlist in the
| specified transaction coordinator. ]
| OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
|
|
|
| I check for MSDTC configuration but it seems right.
|
| I check for this error and i found some KB that indicated a isuee with
| MSDTC, so i test it with the DTCTester but the tool tell work all fine !
| ARGH!!!!
|
| It's important see that the transaction start on the node1 use a "local"
| MSDTC contat a "local" linked server that poit to "local" sql server
| instance for use a "local" DB2.
|
| The MSDTC is cluster configuration.
|
| There is any problem to use a ditribuited transaction with a "local"
linked
| server ?
|
|
| Anybody have any idea ?
|
| Thanks in advance.
|
|
|||Hello,
We wanted to see if the information provided was helpful. Please keep us
posted on your progress and let us know if you have any additional
questions or concerns.
We are looking forward to your response.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 190683365
| References: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Thu, 14 Jun 2007 10:16:25 GMT
| Subject: RE: About SQL2000 SP4 and DTC e Linked Server
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <QonFV0mrHHA.644@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 187
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17954
| NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
|
| Hello,
|
| Thank you for posting here.
|
| From the problem description of the post you submitted, my understanding
| is: when you attempt to start a Distributed Transaction across DB 1 and
DB
| 2, the following error message is received:
|
| ########################
| Server: Msg 7391, Level 16, State 1, Line 2
| The operation could not be performed because the OLE DB provider
'SQLOLEDB'
| was unable to begin a distributed transaction. [OLE/DB provider returned
| message: New transaction cannot enlist in the specified transaction
| coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
| ########################
|
| In addition, you have created a linked server for DB2, which seems work
| properly. If I have misunderstood about your concern, feel free to let me
| know.
|
| Based on my experience, the following error message can normally be
caused
| by one of the following
|
| 1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
| network transactions.
| 2. Windows Firewall is enabled on the computer. By default, Windows
| Firewall blocks the MSDTC program.
| 3. Communication issue between DB1 and DB2
|
| Note This problem may occur even when Windows Firewall is turned off.
|
| In your post, you have indicated that the MSDTC has been verified by the
| DTCTester tool but this tool tell work fine.
|
| First of all, I would like to check whether the object on the destination
| server (DB2) refers back to the first server (DB1). This is what is known
| as a loopback situation. This is not supported, as documented in SQL
Server
| Books Online. For more information, visit the following Microsoft Web
site:
|
| Loopback Linked Servers
| http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
|
| Next, lets manually verify your network name resolution works. Verify
| that the servers can communicate with one another by name, not just by IP
| address. Check in both directions (for example, from DB1 to DB2 and from
| DB2 to DB1). You must resolve all name resolution problems on the network
| before you run your distributed query. This may involve updating WINS,
DNS,
| or LMHost files.
|
| If Windows Firewall or other third party firewall is used, please also
make
| sure that your Remote Procedure Call (RPC) ports are opened correctly.
|
| Below are the steps to configure Windows Firewall to include the MSDTC
| program and to include port 135 as an exception. To do this, follow these
| steps:
| ==============================
| a. Click Start, and then click Run.
| b. In the Run dialog box, type Firewall.cpl , and then click OK
| c. In Control Panel, double-click Windows Firewall.
| d. In the Windows Firewall dialog box, click Add Program on the
Exceptions
| tab.
| e. In the Add a Program dialog box, click the Browse button, and then
| locate the Msdtc.exe file. By default, the file is stored in the
| <Installation drive>:\Windows\System32 folder.
| f. In the Add a Program dialog box, click OK.
| g. In the Windows Firewall dialog box, click to select the msdtc option
in
| the Programs and Services list.
| h. Click Add Port on the Exceptions tab.
| i. In the Add a Port dialog box, type 135 in the Port number text box,
and
| then click to select the TCP option.
| j. In the Add a Port dialog box, type a name for the exception in the
Name
| text box, and then click OK.
| k. In the Windows Firewall dialog box, select the name that you used for
| the exception in step j in the Programs and Services list, and then click
| OK.
|
| In addition, although we have used the DTCTester tool to manually verify
| the MSDTC service, it is also recommended that we manually make sure that
| the Log On As account for the MSDTC service is the Network Service
account
| and it allow the network transaction.
|
| For the detail steps, please refer to the STEP ONE and STEP TWO in the
| following KB article
|
| You receive error 7391 when you run a distributed transaction against a
| linked server in SQL Server 2000 on a computer that is running Windows
| Server 2003
| http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
|
| If anything is unclear in my post, please don't hesitate to let me know
and
| I will be glad to help. Thank you for your efforts and time.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu, MCSE 2000, MCDBA
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| ================================================== ===
| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| ================================================== ===
| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
|
| --
| | From: <alterx@.noemail.noemail>
| | Subject: About SQL2000 SP4 and DTC e Linked Server
| | Date: Wed, 13 Jun 2007 20:50:41 +0200
| | Lines: 60
| | Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | format=flowed;
| | charset="iso-8859-1";
| | reply-type=original
| | Content-Transfer-Encoding: 7bit
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| | X-MS-CommunityGroup-MessageCategory:
| {E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| | X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| | Newsgroups: microsoft.public.sqlserver.server
| | Path: TK2MSFTNGHUB02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| | NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Hi,
| |
| | the scenario is :
| |
| | - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| | - DTC on both server was configured with the modified after the service
| | pack1
| |
| |
| | In SQL 2000 i have 2 database:
| |
| | 1- DB1
| | 2- DB2
| |
| | For the DB2 i have created a linked server and it seems work .
| |
| | Now i would start a distribuited transaction across DB1 & DB2 , the
| | stantment SQL is like this :
| |
| |
| | begin tran
| | insert into table-1
| | select top 100 convert(varchar(100),idMov) as Chiav , num from
| | LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| | commit tran
| |
| |
| |
| | if i run it a error was show :
| |
| | Server: Msg 7391, Level 16, State 1, Line 2
| | The operation could not be performed because the OLE DB provider
| 'SQLOLEDB'
| | was unable to begin a distributed transaction.
| | [OLE/DB provider returned message: New transaction cannot enlist in the
| | specified transaction coordinator. ]
| | OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| | ITransactionJoin::JoinTransaction returned 0x8004d00a].
| |
| |
| |
| | I check for MSDTC configuration but it seems right.
| |
| | I check for this error and i found some KB that indicated a isuee with
| | MSDTC, so i test it with the DTCTester but the tool tell work all fine
!
| | ARGH!!!!
| |
| | It's important see that the transaction start on the node1 use a
"local"
| | MSDTC contat a "local" linked server that poit to "local" sql server
| | instance for use a "local" DB2.
| |
| | The MSDTC is cluster configuration.
| |
| | There is any problem to use a ditribuited transaction with a "local"
| linked
| | server ?
| |
| |
| | Anybody have any idea ?
| |
| | Thanks in advance.
| |
| |
|
|
the scenario is :
- cluster windows 2003 SP1 withc SQL2000 SP4 A/P
- DTC on both server was configured with the modified after the service
pack1
In SQL 2000 i have 2 database:
1- DB1
2- DB2
For the DB2 i have created a linked server and it seems work .
Now i would start a distribuited transaction across DB1 & DB2 , the
stantment SQL is like this :
begin tran
insert into table-1
select top 100 convert(varchar(100),idMov) as Chiav , num from
LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
commit tran
if i run it a error was show :
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I check for MSDTC configuration but it seems right.
I check for this error and i found some KB that indicated a isuee with
MSDTC, so i test it with the DTCTester but the tool tell work all fine !
ARGH!!!!
It's important see that the transaction start on the node1 use a "local"
MSDTC contat a "local" linked server that poit to "local" sql server
instance for use a "local" DB2.
The MSDTC is cluster configuration.
There is any problem to use a ditribuited transaction with a "local" linked
server ?
Anybody have any idea ?
Thanks in advance.
Hi,
You don't mention which KB's you've looked at, but try to see if the 2
links below helps you any further?
http://support.microsoft.com/kb/301600/
http://support.microsoft.com/kb/817064/
Regards
Steen Schlter Persson
Database Administrator / System Administrator
alterx@.noemail.noemail wrote:
> Hi,
> the scenario is :
> - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
> - DTC on both server was configured with the modified after the service
> pack1
>
> In SQL 2000 i have 2 database:
> 1- DB1
> 2- DB2
> For the DB2 i have created a linked server and it seems work .
> Now i would start a distribuited transaction across DB1 & DB2 , the
> stantment SQL is like this :
>
> begin tran
> insert into table-1
> select top 100 convert(varchar(100),idMov) as Chiav , num from
> LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
> commit tran
>
> if i run it a error was show :
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> I check for MSDTC configuration but it seems right.
> I check for this error and i found some KB that indicated a isuee with
> MSDTC, so i test it with the DTCTester but the tool tell work all fine !
> ARGH!!!!
> It's important see that the transaction start on the node1 use a "local"
> MSDTC contat a "local" linked server that poit to "local" sql server
> instance for use a "local" DB2.
> The MSDTC is cluster configuration.
> There is any problem to use a ditribuited transaction with a "local"
> linked server ?
>
> Anybody have any idea ?
> Thanks in advance.
>
|||Hello,
Thank you for posting here.
From the problem description of the post you submitted, my understanding
is: when you attempt to start a Distributed Transaction across DB 1 and DB
2, the following error message is received:
########################
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction. [OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
########################
In addition, you have created a linked server for DB2, which seems work
properly. If I have misunderstood about your concern, feel free to let me
know.
Based on my experience, the following error message can normally be caused
by one of the following
1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
network transactions.
2. Windows Firewall is enabled on the computer. By default, Windows
Firewall blocks the MSDTC program.
3. Communication issue between DB1 and DB2
Note This problem may occur even when Windows Firewall is turned off.
In your post, you have indicated that the MSDTC has been verified by the
DTCTester tool but this tool tell work fine.
First of all, I would like to check whether the object on the destination
server (DB2) refers back to the first server (DB1). This is what is known
as a loopback situation. This is not supported, as documented in SQL Server
Books Online. For more information, visit the following Microsoft Web site:
Loopback Linked Servers
http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
Next, lets manually verify your network name resolution works. Verify
that the servers can communicate with one another by name, not just by IP
address. Check in both directions (for example, from DB1 to DB2 and from
DB2 to DB1). You must resolve all name resolution problems on the network
before you run your distributed query. This may involve updating WINS, DNS,
or LMHost files.
If Windows Firewall or other third party firewall is used, please also make
sure that your Remote Procedure Call (RPC) ports are opened correctly.
Below are the steps to configure Windows Firewall to include the MSDTC
program and to include port 135 as an exception. To do this, follow these
steps:
==============================
a. Click Start, and then click Run.
b. In the Run dialog box, type Firewall.cpl , and then click OK
c. In Control Panel, double-click Windows Firewall.
d. In the Windows Firewall dialog box, click Add Program on the Exceptions
tab.
e. In the Add a Program dialog box, click the Browse button, and then
locate the Msdtc.exe file. By default, the file is stored in the
<Installation drive>:\Windows\System32 folder.
f. In the Add a Program dialog box, click OK.
g. In the Windows Firewall dialog box, click to select the msdtc option in
the Programs and Services list.
h. Click Add Port on the Exceptions tab.
i. In the Add a Port dialog box, type 135 in the Port number text box, and
then click to select the TCP option.
j. In the Add a Port dialog box, type a name for the exception in the Name
text box, and then click OK.
k. In the Windows Firewall dialog box, select the name that you used for
the exception in step j in the Programs and Services list, and then click
OK.
In addition, although we have used the DTCTester tool to manually verify
the MSDTC service, it is also recommended that we manually make sure that
the Log On As account for the MSDTC service is the Network Service account
and it allow the network transaction.
For the detail steps, please refer to the STEP ONE and STEP TWO in the
following KB article
You receive error 7391 when you run a distributed transaction against a
linked server in SQL Server 2000 on a computer that is running Windows
Server 2003
http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
If anything is unclear in my post, please don't hesitate to let me know and
I will be glad to help. Thank you for your efforts and time.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: <alterx@.noemail.noemail>
| Subject: About SQL2000 SP4 and DTC e Linked Server
| Date: Wed, 13 Jun 2007 20:50:41 +0200
| Lines: 60
| Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=original
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| X-MS-CommunityGroup-MessageCategory:
{E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi,
|
| the scenario is :
|
| - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| - DTC on both server was configured with the modified after the service
| pack1
|
|
| In SQL 2000 i have 2 database:
|
| 1- DB1
| 2- DB2
|
| For the DB2 i have created a linked server and it seems work .
|
| Now i would start a distribuited transaction across DB1 & DB2 , the
| stantment SQL is like this :
|
|
| begin tran
| insert into table-1
| select top 100 convert(varchar(100),idMov) as Chiav , num from
| LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| commit tran
|
|
|
| if i run it a error was show :
|
| Server: Msg 7391, Level 16, State 1, Line 2
| The operation could not be performed because the OLE DB provider
'SQLOLEDB'
| was unable to begin a distributed transaction.
| [OLE/DB provider returned message: New transaction cannot enlist in the
| specified transaction coordinator. ]
| OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
|
|
|
| I check for MSDTC configuration but it seems right.
|
| I check for this error and i found some KB that indicated a isuee with
| MSDTC, so i test it with the DTCTester but the tool tell work all fine !
| ARGH!!!!
|
| It's important see that the transaction start on the node1 use a "local"
| MSDTC contat a "local" linked server that poit to "local" sql server
| instance for use a "local" DB2.
|
| The MSDTC is cluster configuration.
|
| There is any problem to use a ditribuited transaction with a "local"
linked
| server ?
|
|
| Anybody have any idea ?
|
| Thanks in advance.
|
|
|||Hello,
We wanted to see if the information provided was helpful. Please keep us
posted on your progress and let us know if you have any additional
questions or concerns.
We are looking forward to your response.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 190683365
| References: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Thu, 14 Jun 2007 10:16:25 GMT
| Subject: RE: About SQL2000 SP4 and DTC e Linked Server
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <QonFV0mrHHA.644@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 187
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17954
| NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
|
| Hello,
|
| Thank you for posting here.
|
| From the problem description of the post you submitted, my understanding
| is: when you attempt to start a Distributed Transaction across DB 1 and
DB
| 2, the following error message is received:
|
| ########################
| Server: Msg 7391, Level 16, State 1, Line 2
| The operation could not be performed because the OLE DB provider
'SQLOLEDB'
| was unable to begin a distributed transaction. [OLE/DB provider returned
| message: New transaction cannot enlist in the specified transaction
| coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
| ########################
|
| In addition, you have created a linked server for DB2, which seems work
| properly. If I have misunderstood about your concern, feel free to let me
| know.
|
| Based on my experience, the following error message can normally be
caused
| by one of the following
|
| 1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
| network transactions.
| 2. Windows Firewall is enabled on the computer. By default, Windows
| Firewall blocks the MSDTC program.
| 3. Communication issue between DB1 and DB2
|
| Note This problem may occur even when Windows Firewall is turned off.
|
| In your post, you have indicated that the MSDTC has been verified by the
| DTCTester tool but this tool tell work fine.
|
| First of all, I would like to check whether the object on the destination
| server (DB2) refers back to the first server (DB1). This is what is known
| as a loopback situation. This is not supported, as documented in SQL
Server
| Books Online. For more information, visit the following Microsoft Web
site:
|
| Loopback Linked Servers
| http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
|
| Next, lets manually verify your network name resolution works. Verify
| that the servers can communicate with one another by name, not just by IP
| address. Check in both directions (for example, from DB1 to DB2 and from
| DB2 to DB1). You must resolve all name resolution problems on the network
| before you run your distributed query. This may involve updating WINS,
DNS,
| or LMHost files.
|
| If Windows Firewall or other third party firewall is used, please also
make
| sure that your Remote Procedure Call (RPC) ports are opened correctly.
|
| Below are the steps to configure Windows Firewall to include the MSDTC
| program and to include port 135 as an exception. To do this, follow these
| steps:
| ==============================
| a. Click Start, and then click Run.
| b. In the Run dialog box, type Firewall.cpl , and then click OK
| c. In Control Panel, double-click Windows Firewall.
| d. In the Windows Firewall dialog box, click Add Program on the
Exceptions
| tab.
| e. In the Add a Program dialog box, click the Browse button, and then
| locate the Msdtc.exe file. By default, the file is stored in the
| <Installation drive>:\Windows\System32 folder.
| f. In the Add a Program dialog box, click OK.
| g. In the Windows Firewall dialog box, click to select the msdtc option
in
| the Programs and Services list.
| h. Click Add Port on the Exceptions tab.
| i. In the Add a Port dialog box, type 135 in the Port number text box,
and
| then click to select the TCP option.
| j. In the Add a Port dialog box, type a name for the exception in the
Name
| text box, and then click OK.
| k. In the Windows Firewall dialog box, select the name that you used for
| the exception in step j in the Programs and Services list, and then click
| OK.
|
| In addition, although we have used the DTCTester tool to manually verify
| the MSDTC service, it is also recommended that we manually make sure that
| the Log On As account for the MSDTC service is the Network Service
account
| and it allow the network transaction.
|
| For the detail steps, please refer to the STEP ONE and STEP TWO in the
| following KB article
|
| You receive error 7391 when you run a distributed transaction against a
| linked server in SQL Server 2000 on a computer that is running Windows
| Server 2003
| http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
|
| If anything is unclear in my post, please don't hesitate to let me know
and
| I will be glad to help. Thank you for your efforts and time.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu, MCSE 2000, MCDBA
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| ================================================== ===
| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| ================================================== ===
| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
|
| --
| | From: <alterx@.noemail.noemail>
| | Subject: About SQL2000 SP4 and DTC e Linked Server
| | Date: Wed, 13 Jun 2007 20:50:41 +0200
| | Lines: 60
| | Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | format=flowed;
| | charset="iso-8859-1";
| | reply-type=original
| | Content-Transfer-Encoding: 7bit
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| | X-MS-CommunityGroup-MessageCategory:
| {E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| | X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| | Newsgroups: microsoft.public.sqlserver.server
| | Path: TK2MSFTNGHUB02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| | NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Hi,
| |
| | the scenario is :
| |
| | - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| | - DTC on both server was configured with the modified after the service
| | pack1
| |
| |
| | In SQL 2000 i have 2 database:
| |
| | 1- DB1
| | 2- DB2
| |
| | For the DB2 i have created a linked server and it seems work .
| |
| | Now i would start a distribuited transaction across DB1 & DB2 , the
| | stantment SQL is like this :
| |
| |
| | begin tran
| | insert into table-1
| | select top 100 convert(varchar(100),idMov) as Chiav , num from
| | LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| | commit tran
| |
| |
| |
| | if i run it a error was show :
| |
| | Server: Msg 7391, Level 16, State 1, Line 2
| | The operation could not be performed because the OLE DB provider
| 'SQLOLEDB'
| | was unable to begin a distributed transaction.
| | [OLE/DB provider returned message: New transaction cannot enlist in the
| | specified transaction coordinator. ]
| | OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| | ITransactionJoin::JoinTransaction returned 0x8004d00a].
| |
| |
| |
| | I check for MSDTC configuration but it seems right.
| |
| | I check for this error and i found some KB that indicated a isuee with
| | MSDTC, so i test it with the DTCTester but the tool tell work all fine
!
| | ARGH!!!!
| |
| | It's important see that the transaction start on the node1 use a
"local"
| | MSDTC contat a "local" linked server that poit to "local" sql server
| | instance for use a "local" DB2.
| |
| | The MSDTC is cluster configuration.
| |
| | There is any problem to use a ditribuited transaction with a "local"
| linked
| | server ?
| |
| |
| | Anybody have any idea ?
| |
| | Thanks in advance.
| |
| |
|
|
About SQL2000 SP4 and DTC e Linked Server
Hi,
the scenario is :
- cluster windows 2003 SP1 withc SQL2000 SP4 A/P
- DTC on both server was configured with the modified after the service
pack1
In SQL 2000 i have 2 database:
1- DB1
2- DB2
For the DB2 i have created a linked server and it seems work .
Now i would start a distribuited transaction across DB1 & DB2 , the
stantment SQL is like this :
begin tran
insert into table-1
select top 100 convert(varchar(100),idMov) as Chiav , num from
LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
commit tran
if i run it a error was show :
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I check for MSDTC configuration but it seems right.
I check for this error and i found some KB that indicated a isuee with
MSDTC, so i test it with the DTCTester but the tool tell work all fine !
ARGH!!!!
It's important see that the transaction start on the node1 use a "local"
MSDTC contat a "local" linked server that poit to "local" sql server
instance for use a "local" DB2.
The MSDTC is cluster configuration.
There is any problem to use a ditribuited transaction with a "local" linked
server ?
Anybody have any idea ?
Thanks in advance.Hi,
You don't mention which KB's you've looked at, but try to see if the 2
links below helps you any further?
http://support.microsoft.com/kb/301600/
http://support.microsoft.com/kb/817064/
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
alterx@.noemail.noemail wrote:
> Hi,
> the scenario is :
> - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
> - DTC on both server was configured with the modified after the service
> pack1
>
> In SQL 2000 i have 2 database:
> 1- DB1
> 2- DB2
> For the DB2 i have created a linked server and it seems work .
> Now i would start a distribuited transaction across DB1 & DB2 , the
> stantment SQL is like this :
>
> begin tran
> insert into table-1
> select top 100 convert(varchar(100),idMov) as Chiav , num from
> LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
> commit tran
>
> if i run it a error was show :
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> I check for MSDTC configuration but it seems right.
> I check for this error and i found some KB that indicated a isuee with
> MSDTC, so i test it with the DTCTester but the tool tell work all fine !
> ARGH!!!!
> It's important see that the transaction start on the node1 use a "local"
> MSDTC contat a "local" linked server that poit to "local" sql server
> instance for use a "local" DB2.
> The MSDTC is cluster configuration.
> There is any problem to use a ditribuited transaction with a "local"
> linked server ?
>
> Anybody have any idea ?
> Thanks in advance.
>|||Hello,
Thank you for posting here.
From the problem description of the post you submitted, my understanding
is: when you attempt to start a Distributed Transaction across DB 1 and DB
2, the following error message is received:
########################
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction. [OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
########################
In addition, you have created a linked server for DB2, which seems work
properly. If I have misunderstood about your concern, feel free to let me
know.
Based on my experience, the following error message can normally be caused
by one of the following
1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
network transactions.
2. Windows Firewall is enabled on the computer. By default, Windows
Firewall blocks the MSDTC program.
3. Communication issue between DB1 and DB2
Note This problem may occur even when Windows Firewall is turned off.
In your post, you have indicated that the MSDTC has been verified by the
DTCTester tool but this tool tell work fine.
First of all, I would like to check whether the object on the destination
server (DB2) refers back to the first server (DB1). This is what is known
as a loopback situation. This is not supported, as documented in SQL Server
Books Online. For more information, visit the following Microsoft Web site:
Loopback Linked Servers
http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
Next, let¡¯s manually verify your network name resolution works. Verify
that the servers can communicate with one another by name, not just by IP
address. Check in both directions (for example, from DB1 to DB2 and from
DB2 to DB1). You must resolve all name resolution problems on the network
before you run your distributed query. This may involve updating WINS, DNS,
or LMHost files.
If Windows Firewall or other third party firewall is used, please also make
sure that your Remote Procedure Call (RPC) ports are opened correctly.
Below are the steps to configure Windows Firewall to include the MSDTC
program and to include port 135 as an exception. To do this, follow these
steps:
==============================a. Click Start, and then click Run.
b. In the Run dialog box, type Firewall.cpl , and then click OK
c. In Control Panel, double-click Windows Firewall.
d. In the Windows Firewall dialog box, click Add Program on the Exceptions
tab.
e. In the Add a Program dialog box, click the Browse button, and then
locate the Msdtc.exe file. By default, the file is stored in the
<Installation drive>:\Windows\System32 folder.
f. In the Add a Program dialog box, click OK.
g. In the Windows Firewall dialog box, click to select the msdtc option in
the Programs and Services list.
h. Click Add Port on the Exceptions tab.
i. In the Add a Port dialog box, type 135 in the Port number text box, and
then click to select the TCP option.
j. In the Add a Port dialog box, type a name for the exception in the Name
text box, and then click OK.
k. In the Windows Firewall dialog box, select the name that you used for
the exception in step j in the Programs and Services list, and then click
OK.
In addition, although we have used the DTCTester tool to manually verify
the MSDTC service, it is also recommended that we manually make sure that
the Log On As account for the MSDTC service is the Network Service account
and it allow the network transaction.
For the detail steps, please refer to the STEP ONE and STEP TWO in the
following KB article
You receive error 7391 when you run a distributed transaction against a
linked server in SQL Server 2000 on a computer that is running Windows
Server 2003
http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
If anything is unclear in my post, please don't hesitate to let me know and
I will be glad to help. Thank you for your efforts and time.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| From: <alterx@.noemail.noemail>
| Subject: About SQL2000 SP4 and DTC e Linked Server
| Date: Wed, 13 Jun 2007 20:50:41 +0200
| Lines: 60
| Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=original
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| X-MS-CommunityGroup-MessageCategory:
{E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi,
|
| the scenario is :
|
| - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| - DTC on both server was configured with the modified after the service
| pack1
|
|
| In SQL 2000 i have 2 database:
|
| 1- DB1
| 2- DB2
|
| For the DB2 i have created a linked server and it seems work .
|
| Now i would start a distribuited transaction across DB1 & DB2 , the
| stantment SQL is like this :
|
|
| begin tran
| insert into table-1
| select top 100 convert(varchar(100),idMov) as Chiav , num from
| LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| commit tran
|
|
|
| if i run it a error was show :
|
| Server: Msg 7391, Level 16, State 1, Line 2
| The operation could not be performed because the OLE DB provider
'SQLOLEDB'
| was unable to begin a distributed transaction.
| [OLE/DB provider returned message: New transaction cannot enlist in the
| specified transaction coordinator. ]
| OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
|
|
|
| I check for MSDTC configuration but it seems right.
|
| I check for this error and i found some KB that indicated a isuee with
| MSDTC, so i test it with the DTCTester but the tool tell work all fine !
| ARGH!!!!
|
| It's important see that the transaction start on the node1 use a "local"
| MSDTC contat a "local" linked server that poit to "local" sql server
| instance for use a "local" DB2.
|
| The MSDTC is cluster configuration.
|
| There is any problem to use a ditribuited transaction with a "local"
linked
| server ?
|
|
| Anybody have any idea ?
|
| Thanks in advance.
|
||||Hello,
We wanted to see if the information provided was helpful. Please keep us
posted on your progress and let us know if you have any additional
questions or concerns.
We are looking forward to your response.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 190683365
| References: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Thu, 14 Jun 2007 10:16:25 GMT
| Subject: RE: About SQL2000 SP4 and DTC e Linked Server
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <QonFV0mrHHA.644@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 187
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17954
| NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
|
| Hello,
|
| Thank you for posting here.
|
| From the problem description of the post you submitted, my understanding
| is: when you attempt to start a Distributed Transaction across DB 1 and
DB
| 2, the following error message is received:
|
| ########################
| Server: Msg 7391, Level 16, State 1, Line 2
| The operation could not be performed because the OLE DB provider
'SQLOLEDB'
| was unable to begin a distributed transaction. [OLE/DB provider returned
| message: New transaction cannot enlist in the specified transaction
| coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
| ########################
|
| In addition, you have created a linked server for DB2, which seems work
| properly. If I have misunderstood about your concern, feel free to let me
| know.
|
| Based on my experience, the following error message can normally be
caused
| by one of the following
|
| 1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
| network transactions.
| 2. Windows Firewall is enabled on the computer. By default, Windows
| Firewall blocks the MSDTC program.
| 3. Communication issue between DB1 and DB2
|
| Note This problem may occur even when Windows Firewall is turned off.
|
| In your post, you have indicated that the MSDTC has been verified by the
| DTCTester tool but this tool tell work fine.
|
| First of all, I would like to check whether the object on the destination
| server (DB2) refers back to the first server (DB1). This is what is known
| as a loopback situation. This is not supported, as documented in SQL
Server
| Books Online. For more information, visit the following Microsoft Web
site:
|
| Loopback Linked Servers
| http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
|
| Next, let¡¯s manually verify your network name resolution works. Verify
| that the servers can communicate with one another by name, not just by IP
| address. Check in both directions (for example, from DB1 to DB2 and from
| DB2 to DB1). You must resolve all name resolution problems on the network
| before you run your distributed query. This may involve updating WINS,
DNS,
| or LMHost files.
|
| If Windows Firewall or other third party firewall is used, please also
make
| sure that your Remote Procedure Call (RPC) ports are opened correctly.
|
| Below are the steps to configure Windows Firewall to include the MSDTC
| program and to include port 135 as an exception. To do this, follow these
| steps:
| ==============================| a. Click Start, and then click Run.
| b. In the Run dialog box, type Firewall.cpl , and then click OK
| c. In Control Panel, double-click Windows Firewall.
| d. In the Windows Firewall dialog box, click Add Program on the
Exceptions
| tab.
| e. In the Add a Program dialog box, click the Browse button, and then
| locate the Msdtc.exe file. By default, the file is stored in the
| <Installation drive>:\Windows\System32 folder.
| f. In the Add a Program dialog box, click OK.
| g. In the Windows Firewall dialog box, click to select the msdtc option
in
| the Programs and Services list.
| h. Click Add Port on the Exceptions tab.
| i. In the Add a Port dialog box, type 135 in the Port number text box,
and
| then click to select the TCP option.
| j. In the Add a Port dialog box, type a name for the exception in the
Name
| text box, and then click OK.
| k. In the Windows Firewall dialog box, select the name that you used for
| the exception in step j in the Programs and Services list, and then click
| OK.
|
| In addition, although we have used the DTCTester tool to manually verify
| the MSDTC service, it is also recommended that we manually make sure that
| the Log On As account for the MSDTC service is the Network Service
account
| and it allow the network transaction.
|
| For the detail steps, please refer to the STEP ONE and STEP TWO in the
| following KB article
|
| You receive error 7391 when you run a distributed transaction against a
| linked server in SQL Server 2000 on a computer that is running Windows
| Server 2003
| http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
|
| If anything is unclear in my post, please don't hesitate to let me know
and
| I will be glad to help. Thank you for your efforts and time.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu, MCSE 2000, MCDBA
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| =====================================================| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| =====================================================| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
|
| --
| | From: <alterx@.noemail.noemail>
| | Subject: About SQL2000 SP4 and DTC e Linked Server
| | Date: Wed, 13 Jun 2007 20:50:41 +0200
| | Lines: 60
| | Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | format=flowed;
| | charset="iso-8859-1";
| | reply-type=original
| | Content-Transfer-Encoding: 7bit
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| | X-MS-CommunityGroup-MessageCategory:
| {E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| | X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| | Newsgroups: microsoft.public.sqlserver.server
| | Path: TK2MSFTNGHUB02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| | NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Hi,
| |
| | the scenario is :
| |
| | - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| | - DTC on both server was configured with the modified after the service
| | pack1
| |
| |
| | In SQL 2000 i have 2 database:
| |
| | 1- DB1
| | 2- DB2
| |
| | For the DB2 i have created a linked server and it seems work .
| |
| | Now i would start a distribuited transaction across DB1 & DB2 , the
| | stantment SQL is like this :
| |
| |
| | begin tran
| | insert into table-1
| | select top 100 convert(varchar(100),idMov) as Chiav , num from
| | LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| | commit tran
| |
| |
| |
| | if i run it a error was show :
| |
| | Server: Msg 7391, Level 16, State 1, Line 2
| | The operation could not be performed because the OLE DB provider
| 'SQLOLEDB'
| | was unable to begin a distributed transaction.
| | [OLE/DB provider returned message: New transaction cannot enlist in the
| | specified transaction coordinator. ]
| | OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| | ITransactionJoin::JoinTransaction returned 0x8004d00a].
| |
| |
| |
| | I check for MSDTC configuration but it seems right.
| |
| | I check for this error and i found some KB that indicated a isuee with
| | MSDTC, so i test it with the DTCTester but the tool tell work all fine
!
| | ARGH!!!!
| |
| | It's important see that the transaction start on the node1 use a
"local"
| | MSDTC contat a "local" linked server that poit to "local" sql server
| | instance for use a "local" DB2.
| |
| | The MSDTC is cluster configuration.
| |
| | There is any problem to use a ditribuited transaction with a "local"
| linked
| | server ?
| |
| |
| | Anybody have any idea ?
| |
| | Thanks in advance.
| |
| |
|
|
the scenario is :
- cluster windows 2003 SP1 withc SQL2000 SP4 A/P
- DTC on both server was configured with the modified after the service
pack1
In SQL 2000 i have 2 database:
1- DB1
2- DB2
For the DB2 i have created a linked server and it seems work .
Now i would start a distribuited transaction across DB1 & DB2 , the
stantment SQL is like this :
begin tran
insert into table-1
select top 100 convert(varchar(100),idMov) as Chiav , num from
LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
commit tran
if i run it a error was show :
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I check for MSDTC configuration but it seems right.
I check for this error and i found some KB that indicated a isuee with
MSDTC, so i test it with the DTCTester but the tool tell work all fine !
ARGH!!!!
It's important see that the transaction start on the node1 use a "local"
MSDTC contat a "local" linked server that poit to "local" sql server
instance for use a "local" DB2.
The MSDTC is cluster configuration.
There is any problem to use a ditribuited transaction with a "local" linked
server ?
Anybody have any idea ?
Thanks in advance.Hi,
You don't mention which KB's you've looked at, but try to see if the 2
links below helps you any further?
http://support.microsoft.com/kb/301600/
http://support.microsoft.com/kb/817064/
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
alterx@.noemail.noemail wrote:
> Hi,
> the scenario is :
> - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
> - DTC on both server was configured with the modified after the service
> pack1
>
> In SQL 2000 i have 2 database:
> 1- DB1
> 2- DB2
> For the DB2 i have created a linked server and it seems work .
> Now i would start a distribuited transaction across DB1 & DB2 , the
> stantment SQL is like this :
>
> begin tran
> insert into table-1
> select top 100 convert(varchar(100),idMov) as Chiav , num from
> LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
> commit tran
>
> if i run it a error was show :
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> I check for MSDTC configuration but it seems right.
> I check for this error and i found some KB that indicated a isuee with
> MSDTC, so i test it with the DTCTester but the tool tell work all fine !
> ARGH!!!!
> It's important see that the transaction start on the node1 use a "local"
> MSDTC contat a "local" linked server that poit to "local" sql server
> instance for use a "local" DB2.
> The MSDTC is cluster configuration.
> There is any problem to use a ditribuited transaction with a "local"
> linked server ?
>
> Anybody have any idea ?
> Thanks in advance.
>|||Hello,
Thank you for posting here.
From the problem description of the post you submitted, my understanding
is: when you attempt to start a Distributed Transaction across DB 1 and DB
2, the following error message is received:
########################
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction. [OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
########################
In addition, you have created a linked server for DB2, which seems work
properly. If I have misunderstood about your concern, feel free to let me
know.
Based on my experience, the following error message can normally be caused
by one of the following
1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
network transactions.
2. Windows Firewall is enabled on the computer. By default, Windows
Firewall blocks the MSDTC program.
3. Communication issue between DB1 and DB2
Note This problem may occur even when Windows Firewall is turned off.
In your post, you have indicated that the MSDTC has been verified by the
DTCTester tool but this tool tell work fine.
First of all, I would like to check whether the object on the destination
server (DB2) refers back to the first server (DB1). This is what is known
as a loopback situation. This is not supported, as documented in SQL Server
Books Online. For more information, visit the following Microsoft Web site:
Loopback Linked Servers
http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
Next, let¡¯s manually verify your network name resolution works. Verify
that the servers can communicate with one another by name, not just by IP
address. Check in both directions (for example, from DB1 to DB2 and from
DB2 to DB1). You must resolve all name resolution problems on the network
before you run your distributed query. This may involve updating WINS, DNS,
or LMHost files.
If Windows Firewall or other third party firewall is used, please also make
sure that your Remote Procedure Call (RPC) ports are opened correctly.
Below are the steps to configure Windows Firewall to include the MSDTC
program and to include port 135 as an exception. To do this, follow these
steps:
==============================a. Click Start, and then click Run.
b. In the Run dialog box, type Firewall.cpl , and then click OK
c. In Control Panel, double-click Windows Firewall.
d. In the Windows Firewall dialog box, click Add Program on the Exceptions
tab.
e. In the Add a Program dialog box, click the Browse button, and then
locate the Msdtc.exe file. By default, the file is stored in the
<Installation drive>:\Windows\System32 folder.
f. In the Add a Program dialog box, click OK.
g. In the Windows Firewall dialog box, click to select the msdtc option in
the Programs and Services list.
h. Click Add Port on the Exceptions tab.
i. In the Add a Port dialog box, type 135 in the Port number text box, and
then click to select the TCP option.
j. In the Add a Port dialog box, type a name for the exception in the Name
text box, and then click OK.
k. In the Windows Firewall dialog box, select the name that you used for
the exception in step j in the Programs and Services list, and then click
OK.
In addition, although we have used the DTCTester tool to manually verify
the MSDTC service, it is also recommended that we manually make sure that
the Log On As account for the MSDTC service is the Network Service account
and it allow the network transaction.
For the detail steps, please refer to the STEP ONE and STEP TWO in the
following KB article
You receive error 7391 when you run a distributed transaction against a
linked server in SQL Server 2000 on a computer that is running Windows
Server 2003
http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
If anything is unclear in my post, please don't hesitate to let me know and
I will be glad to help. Thank you for your efforts and time.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| From: <alterx@.noemail.noemail>
| Subject: About SQL2000 SP4 and DTC e Linked Server
| Date: Wed, 13 Jun 2007 20:50:41 +0200
| Lines: 60
| Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=original
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| X-MS-CommunityGroup-MessageCategory:
{E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi,
|
| the scenario is :
|
| - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| - DTC on both server was configured with the modified after the service
| pack1
|
|
| In SQL 2000 i have 2 database:
|
| 1- DB1
| 2- DB2
|
| For the DB2 i have created a linked server and it seems work .
|
| Now i would start a distribuited transaction across DB1 & DB2 , the
| stantment SQL is like this :
|
|
| begin tran
| insert into table-1
| select top 100 convert(varchar(100),idMov) as Chiav , num from
| LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| commit tran
|
|
|
| if i run it a error was show :
|
| Server: Msg 7391, Level 16, State 1, Line 2
| The operation could not be performed because the OLE DB provider
'SQLOLEDB'
| was unable to begin a distributed transaction.
| [OLE/DB provider returned message: New transaction cannot enlist in the
| specified transaction coordinator. ]
| OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
|
|
|
| I check for MSDTC configuration but it seems right.
|
| I check for this error and i found some KB that indicated a isuee with
| MSDTC, so i test it with the DTCTester but the tool tell work all fine !
| ARGH!!!!
|
| It's important see that the transaction start on the node1 use a "local"
| MSDTC contat a "local" linked server that poit to "local" sql server
| instance for use a "local" DB2.
|
| The MSDTC is cluster configuration.
|
| There is any problem to use a ditribuited transaction with a "local"
linked
| server ?
|
|
| Anybody have any idea ?
|
| Thanks in advance.
|
||||Hello,
We wanted to see if the information provided was helpful. Please keep us
posted on your progress and let us know if you have any additional
questions or concerns.
We are looking forward to your response.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 190683365
| References: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Thu, 14 Jun 2007 10:16:25 GMT
| Subject: RE: About SQL2000 SP4 and DTC e Linked Server
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <QonFV0mrHHA.644@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 187
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17954
| NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
|
| Hello,
|
| Thank you for posting here.
|
| From the problem description of the post you submitted, my understanding
| is: when you attempt to start a Distributed Transaction across DB 1 and
DB
| 2, the following error message is received:
|
| ########################
| Server: Msg 7391, Level 16, State 1, Line 2
| The operation could not be performed because the OLE DB provider
'SQLOLEDB'
| was unable to begin a distributed transaction. [OLE/DB provider returned
| message: New transaction cannot enlist in the specified transaction
| coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
| ########################
|
| In addition, you have created a linked server for DB2, which seems work
| properly. If I have misunderstood about your concern, feel free to let me
| know.
|
| Based on my experience, the following error message can normally be
caused
| by one of the following
|
| 1. Microsoft Distributed Transaction Coordinator (MSDTC) is disabled for
| network transactions.
| 2. Windows Firewall is enabled on the computer. By default, Windows
| Firewall blocks the MSDTC program.
| 3. Communication issue between DB1 and DB2
|
| Note This problem may occur even when Windows Firewall is turned off.
|
| In your post, you have indicated that the MSDTC has been verified by the
| DTCTester tool but this tool tell work fine.
|
| First of all, I would like to check whether the object on the destination
| server (DB2) refers back to the first server (DB1). This is what is known
| as a loopback situation. This is not supported, as documented in SQL
Server
| Books Online. For more information, visit the following Microsoft Web
site:
|
| Loopback Linked Servers
| http://msdn2.microsoft.com/en-us/library/aa213286(SQL.80).aspx
|
| Next, let¡¯s manually verify your network name resolution works. Verify
| that the servers can communicate with one another by name, not just by IP
| address. Check in both directions (for example, from DB1 to DB2 and from
| DB2 to DB1). You must resolve all name resolution problems on the network
| before you run your distributed query. This may involve updating WINS,
DNS,
| or LMHost files.
|
| If Windows Firewall or other third party firewall is used, please also
make
| sure that your Remote Procedure Call (RPC) ports are opened correctly.
|
| Below are the steps to configure Windows Firewall to include the MSDTC
| program and to include port 135 as an exception. To do this, follow these
| steps:
| ==============================| a. Click Start, and then click Run.
| b. In the Run dialog box, type Firewall.cpl , and then click OK
| c. In Control Panel, double-click Windows Firewall.
| d. In the Windows Firewall dialog box, click Add Program on the
Exceptions
| tab.
| e. In the Add a Program dialog box, click the Browse button, and then
| locate the Msdtc.exe file. By default, the file is stored in the
| <Installation drive>:\Windows\System32 folder.
| f. In the Add a Program dialog box, click OK.
| g. In the Windows Firewall dialog box, click to select the msdtc option
in
| the Programs and Services list.
| h. Click Add Port on the Exceptions tab.
| i. In the Add a Port dialog box, type 135 in the Port number text box,
and
| then click to select the TCP option.
| j. In the Add a Port dialog box, type a name for the exception in the
Name
| text box, and then click OK.
| k. In the Windows Firewall dialog box, select the name that you used for
| the exception in step j in the Programs and Services list, and then click
| OK.
|
| In addition, although we have used the DTCTester tool to manually verify
| the MSDTC service, it is also recommended that we manually make sure that
| the Log On As account for the MSDTC service is the Network Service
account
| and it allow the network transaction.
|
| For the detail steps, please refer to the STEP ONE and STEP TWO in the
| following KB article
|
| You receive error 7391 when you run a distributed transaction against a
| linked server in SQL Server 2000 on a computer that is running Windows
| Server 2003
| http://support.microsoft.com/default.aspx?scid=KB;EN-US;329332
|
| If anything is unclear in my post, please don't hesitate to let me know
and
| I will be glad to help. Thank you for your efforts and time.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu, MCSE 2000, MCDBA
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| =====================================================| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| =====================================================| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
|
| --
| | From: <alterx@.noemail.noemail>
| | Subject: About SQL2000 SP4 and DTC e Linked Server
| | Date: Wed, 13 Jun 2007 20:50:41 +0200
| | Lines: 60
| | Message-ID: <8083FF78-EEF1-4280-ADAB-B497217EA3DF@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | format=flowed;
| | charset="iso-8859-1";
| | reply-type=original
| | Content-Transfer-Encoding: 7bit
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
| | X-MS-CommunityGroup-MessageCategory:
| {E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| | X-MS-CommunityGroup-PostID: {8083FF78-EEF1-4280-ADAB-B497217EA3DF}
| | Newsgroups: microsoft.public.sqlserver.server
| | Path: TK2MSFTNGHUB02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:17889
| | NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Hi,
| |
| | the scenario is :
| |
| | - cluster windows 2003 SP1 withc SQL2000 SP4 A/P
| | - DTC on both server was configured with the modified after the service
| | pack1
| |
| |
| | In SQL 2000 i have 2 database:
| |
| | 1- DB1
| | 2- DB2
| |
| | For the DB2 i have created a linked server and it seems work .
| |
| | Now i would start a distribuited transaction across DB1 & DB2 , the
| | stantment SQL is like this :
| |
| |
| | begin tran
| | insert into table-1
| | select top 100 convert(varchar(100),idMov) as Chiav , num from
| | LINKEDSRV1.DB2.dbo.tbl-order where num >100 and num<=1000
| | commit tran
| |
| |
| |
| | if i run it a error was show :
| |
| | Server: Msg 7391, Level 16, State 1, Line 2
| | The operation could not be performed because the OLE DB provider
| 'SQLOLEDB'
| | was unable to begin a distributed transaction.
| | [OLE/DB provider returned message: New transaction cannot enlist in the
| | specified transaction coordinator. ]
| | OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| | ITransactionJoin::JoinTransaction returned 0x8004d00a].
| |
| |
| |
| | I check for MSDTC configuration but it seems right.
| |
| | I check for this error and i found some KB that indicated a isuee with
| | MSDTC, so i test it with the DTCTester but the tool tell work all fine
!
| | ARGH!!!!
| |
| | It's important see that the transaction start on the node1 use a
"local"
| | MSDTC contat a "local" linked server that poit to "local" sql server
| | instance for use a "local" DB2.
| |
| | The MSDTC is cluster configuration.
| |
| | There is any problem to use a ditribuited transaction with a "local"
| linked
| | server ?
| |
| |
| | Anybody have any idea ?
| |
| | Thanks in advance.
| |
| |
|
|
Subscribe to:
Posts (Atom)