Hi all
I am not sure if this is a Windows 2003 server problem or a SQL Server
problem so I am cross posting to both groups.
I have a new Dell server which is running Windows 2003 and is going to be a
development database server. I have SQL Server 2000 installed on it and i
can connect using the SQL client tools manipulate data on that server
without any problems.
The problem comes when I try and connect using an ODBC connection from
within an MTS/COM+ component. I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '8004d00a'
[Microsoft][ODBC SQL Server Driver]Distributed transaction error
While searching for a solution to this problem, I came across a Microsoft
utility (available from
http://support.microsoft.com/defaul...b;EN-US;Q293799) which has
helped me narrow this problem down to it not being my COM+ component because
when I try and run this tool I get the following output:
Executed: dtctester
DSN: <My DSN Name>
User Name: sa
Password: <My sa Password>
tablename= #dtc32294
Creating Temp Table for Testing: #dtc32294
Warning: No Columns in Result Set From Executing: 'create table #dtc32294
(ival int)'
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Error:
SQLSTATE=25S12,Native error=-2147168219,msg='[Microsoft][ODBC SQL Se
rver
Driver]Distributed transaction error'
Error: SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server
Driver]Invalid cur
sor state
Typical Errors in DTC Output When
a. Firewall Has Ports Closed
-OR-
b. Bad WINS/DNS entries
-OR-
c. Misconfigured network
-OR-
d. Misconfigured SQL Server machine that has multiple netcards.
Aborting DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.
My feeling is that it is specific to Windows 2003 because I can perform
exactly the same test using dtctester.exe to our old SQL server from the
same client and using the same ODBC DSN (with the server name changed of
course) and everything works without problems.
Thanks in advance for any help you can give,
DaveAdditional Information which I forgot to add in the original post.
In response to the suggestions the dtctester app gives me:
a. Firewall Has Ports Closed
- There is no firewall in between the two machines
b. Bad WINS/DNS entries
- These are fine and checked
c. Misconfigured network
- I can communicate with the server over everything apart from DTC
transactions
d. Misconfigured SQL Server machine that has multiple netcards.
- This is too vague to really know what they mean but I have disabled every
other network card in the machine so there is only one active local
connection.
"Dave Verwer" <d.verwer@.link-hrsystems.com> wrote in message
news:ObEBOsECEHA.684@.tk2msftngp13.phx.gbl...
> Hi all
> I am not sure if this is a Windows 2003 server problem or a SQL Server
> problem so I am cross posting to both groups.
> I have a new Dell server which is running Windows 2003 and is going to be
a
> development database server. I have SQL Server 2000 installed on it and i
> can connect using the SQL client tools manipulate data on that server
> without any problems.
> The problem comes when I try and connect using an ODBC connection from
> within an MTS/COM+ component. I get the following error:
> Microsoft OLE DB Provider for ODBC Drivers error '8004d00a'
> [Microsoft][ODBC SQL Server Driver]Distributed transaction error
> While searching for a solution to this problem, I came across a Microsoft
> utility (available from
> http://support.microsoft.com/defaul...b;EN-US;Q293799) which has
> helped me narrow this problem down to it not being my COM+ component
because
> when I try and run this tool I get the following output:
> Executed: dtctester
> DSN: <My DSN Name>
> User Name: sa
> Password: <My sa Password>
> tablename= #dtc32294
> Creating Temp Table for Testing: #dtc32294
> Warning: No Columns in Result Set From Executing: 'create table #dtc32294
> (ival int)'
> Initializing DTC
> Beginning DTC Transaction
> Enlisting Connection in Transaction
> Error:
> SQLSTATE=25S12,Native error=-2147168219,msg='[Microsoft][ODBC SQL
Server
> Driver]Distributed transaction error'
> Error: SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Serv
er
> Driver]Invalid cur
> sor state
> Typical Errors in DTC Output When
> a. Firewall Has Ports Closed
> -OR-
> b. Bad WINS/DNS entries
> -OR-
> c. Misconfigured network
> -OR-
> d. Misconfigured SQL Server machine that has multiple netcards.
> Aborting DTC Transaction
> Releasing DTC Interface Pointers
> Successfully Released pTransaction Pointer.
> My feeling is that it is specific to Windows 2003 because I can perform
> exactly the same test using dtctester.exe to our old SQL server from the
> same client and using the same ODBC DSN (with the server name changed of
> course) and everything works without problems.
> Thanks in advance for any help you can give,
> Dave
>|||Hi Dave,
Thanks for your post. If I understand correctly you got the following error
message when you tried and connect using an ODBC connection form within an
MTS/COM+ component.
Microsoft OLE DB Provider for ODBC Drivers error '8004d00a'
[Microsoft][ODBC SQL Server Driver]Distributed transaction error
Based on my research, by default, network DTC access is disabled on the
Windows Server 2003 products. When you do not enable network DTC access on
the server, applications can only use transactions that stay on the local
computer. Please try the suggestions in the following article and do not
forget to restart your computer after enabling network DTC access.
817064 HOW TO: Enable Network DTC Access in Windows Server 2003
http://support.microsoft.com/?id=817064
Please feel free to post in the group if this solves your problem or if you
would like further assistance on this issue.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Thanks for the reply
The database server did not have this option ticked so i ticked it and
rebooted it however i still get the same error using the dtctester
application.
Dave
""Yuan Shao"" wrote in message news:elofZuMCEHA.600@.cpmsftngxa06.phx.gbl...
> Hi Dave,
> Thanks for your post. If I understand correctly you got the following
error
> message when you tried and connect using an ODBC connection form within an
> MTS/COM+ component.
> Microsoft OLE DB Provider for ODBC Drivers error '8004d00a'
> [Microsoft][ODBC SQL Server Driver]Distributed transaction error
> Based on my research, by default, network DTC access is disabled on the
> Windows Server 2003 products. When you do not enable network DTC access on
> the server, applications can only use transactions that stay on the local
> computer. Please try the suggestions in the following article and do not
> forget to restart your computer after enabling network DTC access.
> 817064 HOW TO: Enable Network DTC Access in Windows Server 2003
> http://support.microsoft.com/?id=817064
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance on this issue.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Additional Information:
I tried the dtctester application from the database server itself and it
worked so this is definately a remote access DTC issue.
"Dave Verwer" <d.verwer@.link-hrsystems.com> wrote in message
news:%231n5tknCEHA.2600@.TK2MSFTNGP12.phx.gbl...
> Thanks for the reply
> The database server did not have this option ticked so i ticked it and
> rebooted it however i still get the same error using the dtctester
> application.
> Dave
>
> ""Yuan Shao"" wrote in message
news:elofZuMCEHA.600@.cpmsftngxa06.phx.gbl...
> error
an
on
local
> you
rights.
>|||Hi Dave,
Thanks for your feedback. Please check the following registry keys on your
windows 2003 server.
HKLM\Software\Microsoft\MSDTC\Security\N
etworkDTCAccess
NetworkDTCAccessAdmin
NetworkDTCAccessTransactions
Please set them to 1 to enable these registry key and restarted MSDTC & SQL
Server.
I am looking forward to hearing from you soon.
Sincerely,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Just to clarify, you want me to add a new Key under the
HKLM\Software\Microsoft\MSDTC\Security\ key called NetworkDTCAccess (this
key/folder does not exist on my server) and then add 2 DWORD values under
there called NetworkDTCAccessAdmin and NetworkDTCAccessTransactions and set
them both to value 1?
I am asking because i am not sure if you meant for the NetworkDTCAccess key
to already exist and if that signifies a more significant problem than we
are dealing with here.
""Yuan Shao"" <v-yshao@.online.microsoft.com> wrote in message
news:RWKN1twCEHA.2300@.cpmsftngxa06.phx.gbl...
> Hi Dave,
> Thanks for your feedback. Please check the following registry keys on your
> windows 2003 server.
> HKLM\Software\Microsoft\MSDTC\Security\N
etworkDTCAccess
> NetworkDTCAccessAdmin
> NetworkDTCAccessTransactions
> Please set them to 1 to enable these registry key and restarted MSDTC &
SQL
> Server.
> I am looking forward to hearing from you soon.
> Sincerely,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Dave,
Thanks for your update. I apologize for my confusion. In left detail pane
of the HKLM\Software\Microsoft\MSDTC\Security\ folder, you are able to see
3 DWORD values (NetworkDTCAccess, NetworkDTCAccessAdmin and
NetworkDTCAccessTransactions), which exist on my windows 2003 computer.
Please make sure the values of them are 1.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Dave,
How is the issue going on your side? Let us know if you need further
assistance on this issue.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.sql
Showing posts with label alli. Show all posts
Showing posts with label alli. Show all posts
Wednesday, March 28, 2012
Saturday, February 25, 2012
Problem with query
Hi All!
i want to update a column based on another column and using following
quey but its giving me error any help
update hospital1 set hospital1.father_name = temp.father_name
join temp on temp.id = temp.id
and hospital1.father_name <> 'Not Available'
thanx!
Farid
*** Sent via Developersdex http://www.examnotes.net ***update h
set h.father_name = t.father_name
from hospital1 h
join temp t on t.id = h.id
and h.father_name <> 'Not Available'
I assumed you can join temp and hospital1 on id
http://sqlservercode.blogspot.com/|||Ghulam Farid wrote:
> Hi All!
> i want to update a column based on another column and using following
> quey but its giving me error any help
> update hospital1 set hospital1.father_name = temp.father_name
> join temp on temp.id = temp.id
> and hospital1.father_name <> 'Not Available'
> thanx!
> Farid
>
> *** Sent via Developersdex http://www.examnotes.net ***
I'm guessing you want something like this:
UPDATE hospital1
SET father_name =
(SELECT father_name
FROM temp
WHERE id = hospital1.id
AND father_name <> 'Not Available') ;
WHERE ... ?
Note that this statement assumes id is unique in Temp for each hospital
row to be updated. If that assumption is incorrect then please explain
how you want to handle the duplicates (i.e. more than one father_name).
David Portas
SQL Server MVP
--|||Your proprietary answer makes no sense in terms of the SQL language
model. A FROM clause is always suppose effectively materialize a
working table that disappears at the end of the statement. Likewise,
an alias is supposed to act as it materializes a new working table with
the data from the original table expression in it. To be consistent,
this syntax says that you have done nothing to the base table.
Sybase and some other vendors had the same syntax but with different
semantics. Worst of both worlds!
And on top of that, it is unpredictable. This is a simple example from
Adam Machanic
CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Foo VALUES ('A', 0);
INSERT INTO Foo VALUES ('B', 0);
INSERT INTO Foo VALUES ('C', 0);
CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Bar VALUES ('A', 1);
INSERT INTO Bar VALUES ('A', 2);
INSERT INTO Bar VALUES ('B', 1);
INSERT INTO Bar VALUES ('C', 1);
You run this proprietary UPDATE with a FROM clause:
UPDATE Foo
SET Foo.col_b = Bar.col_b
FROM Foo INNER JOIN Bar
ON Foo.col_a = Bar.col_a;
The result of the update cannot be determined. The value of the column
will depend upon either order of insertion, (if there are no clustered
indexes present), or on order of clustering (but only if the cluster
isn't fragmented).
i want to update a column based on another column and using following
quey but its giving me error any help
update hospital1 set hospital1.father_name = temp.father_name
join temp on temp.id = temp.id
and hospital1.father_name <> 'Not Available'
thanx!
Farid
*** Sent via Developersdex http://www.examnotes.net ***update h
set h.father_name = t.father_name
from hospital1 h
join temp t on t.id = h.id
and h.father_name <> 'Not Available'
I assumed you can join temp and hospital1 on id
http://sqlservercode.blogspot.com/|||Ghulam Farid wrote:
> Hi All!
> i want to update a column based on another column and using following
> quey but its giving me error any help
> update hospital1 set hospital1.father_name = temp.father_name
> join temp on temp.id = temp.id
> and hospital1.father_name <> 'Not Available'
> thanx!
> Farid
>
> *** Sent via Developersdex http://www.examnotes.net ***
I'm guessing you want something like this:
UPDATE hospital1
SET father_name =
(SELECT father_name
FROM temp
WHERE id = hospital1.id
AND father_name <> 'Not Available') ;
WHERE ... ?
Note that this statement assumes id is unique in Temp for each hospital
row to be updated. If that assumption is incorrect then please explain
how you want to handle the duplicates (i.e. more than one father_name).
David Portas
SQL Server MVP
--|||Your proprietary answer makes no sense in terms of the SQL language
model. A FROM clause is always suppose effectively materialize a
working table that disappears at the end of the statement. Likewise,
an alias is supposed to act as it materializes a new working table with
the data from the original table expression in it. To be consistent,
this syntax says that you have done nothing to the base table.
Sybase and some other vendors had the same syntax but with different
semantics. Worst of both worlds!
And on top of that, it is unpredictable. This is a simple example from
Adam Machanic
CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Foo VALUES ('A', 0);
INSERT INTO Foo VALUES ('B', 0);
INSERT INTO Foo VALUES ('C', 0);
CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Bar VALUES ('A', 1);
INSERT INTO Bar VALUES ('A', 2);
INSERT INTO Bar VALUES ('B', 1);
INSERT INTO Bar VALUES ('C', 1);
You run this proprietary UPDATE with a FROM clause:
UPDATE Foo
SET Foo.col_b = Bar.col_b
FROM Foo INNER JOIN Bar
ON Foo.col_a = Bar.col_a;
The result of the update cannot be determined. The value of the column
will depend upon either order of insertion, (if there are no clustered
indexes present), or on order of clustering (but only if the cluster
isn't fragmented).
Monday, February 20, 2012
Problem with PAGE HEADER
Dear All
I am Sundaraguru from India.
I have problem with Reporting services header showing time..
I designed the one report. That report Size is 8inch x 41 inches..
What are problems i having in this reports:
1. Report header is not coming in second pages. ( For report header i am not using the report header area, Because the header informations will varying based on the data's. That's Why I placed in the detail sections)
2. While Seeing the preview time it is showing only 11 page ( for 2 record) after exporting to PDF format it showing 32 pages.
How i can solve the above problem.. I have to get is all the page's i have to show report header in all the pages.
I am using SQL Server 2005 , & VS.Net 2005 With November 2006 CTP
And Send your valuable suggestions to following mail id's sundaraguru_s@.apollohealthstreet.com and sundharmail@.yahoo.co.in.
Advance Thanks
Kind Regards
Sundaraguru S
Dear All,
I got the Solution
I place the Repeatable information to the one separate rectangle.. Right the Rectangle -> General -> Data Region (Combo) Select the dataset. (If the Selected dataset is contains then detailed information). And set the hidden property for the rectangle
Add the header and footer information and refer the hidden rectangle contained control informations(Using ReportItems("").Value ).
Kind Regards
Sundaraguru S
Problem with PAGE HEADER
Dear All
I am Sundaraguru from India.
I have problem with Reporting services header showing time..
I designed the one report. That report Size is 8inch x 41 inches..
What are problems i having in this reports:
1. Report header is not coming in second pages. ( For report header i am not using the report header area, Because the header informations will varying based on the data's. That's Why I placed in the detail sections)
2. While Seeing the preview time it is showing only 11 page ( for 2 record) after exporting to PDF format it showing 32 pages.
How i can solve the above problem.. I have to get is all the page's i have to show report header in all the pages.
I am using SQL Server 2005 , & VS.Net 2005 With November 2006 CTP
And Send your valuable suggestions to following mail id's sundaraguru_s@.apollohealthstreet.com and sundharmail@.yahoo.co.in.
Advance Thanks
Kind Regards
Sundaraguru S
Dear All,
I got the Solution
I place the Repeatable information to the one separate rectangle.. Right the Rectangle -> General -> Data Region (Combo) Select the dataset. (If the Selected dataset is contains then detailed information). And set the hidden property for the rectangle
Add the header and footer information and refer the hidden rectangle contained control informations(Using ReportItems("").Value ).
Kind Regards
Sundaraguru S
Subscribe to:
Posts (Atom)