Combining Two Tables Via T-SQL

Hello,

I have two tables that have different column names so I can not combine them using UNION statement. Is there a way to combine two tables and have all the columns from both tables.

Thank you for your help!

UNION does not require that the column names be the same, only that the datatypes are similar enough to combine. See this Example:

Code Snippet

USE Northwind
GO

SELECT CompanyName FROM Customers
UNION
SELECT FirstName + ' ' + LastName FROM Employees

CompanyName
-
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
Andrew Fuller
Anne Dodsworth
Antonio Moreno Taquería
Around the Horn
Berglunds snabbk?p
Bill Smith
...

|||

Donnie:

The column names do not have to be the same for you to union together two tables. If you are trying to union together to tables column-for-column, it is sufficient to have:

The number of columns the same The datatypes of corresponding columns be the same|||

Can you expand on what you are trying to accomplish?. As long as the data type of the columns be the same, including collation, then there is no problem using union or "union all".

declare @.t1 table(c1 int, c2 int)

declare @.t2 table(c3 int, c4 int)

insertinto @.t1 values(1, 2)

insertinto @.t2 values(3, 4)

select c1, c2 from @.t1

union all

select c3, c4 from @.t2

AMB

|||

Are you certain that it is a UNION that you need to perform, and not a JOIN?

A JOIN will allow you to return all columns from both tables as individual columns within the same resultset (i.e. merge the data vertically), like so:

Code Snippet

Table 1 - Sample Data

Column1a Column2a Column3a

--

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

Table 2 - Sample Data

Column1b Column2b Column3b

--

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

Output

Column1a Column2a Column3a Column1b Column2b Column3b

--

1 T1C2R1 T1C3R1 1 T2C2R1 T2C3R1

2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2

3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3

SELECT t1.Column1a,
t1.Column2a,
t1.Column3a,
t2.Column1b,
t2.Column2b,
t2.Column3b
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Column1a = t2.Column1b

A UNION will allow you to horizontally merge the data from both tables, like so:

Code Snippet

Table 1 - Sample Data

Column1a Column2a Column3a

--

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

Table 2 - Sample Data

Column1b Column2b Column3b

--

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

Output

Column1 Column2 Column3

-

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

SELECT t1.Column1a AS Column1,
t1.Column2a AS Column2,
t1.Column3a AS Column3

FROM Table1 t1

UNION ALL

SELECT t2.Column1b,
t2.Column2b,
t2.Column3b

FROM Table2 t2

Chris|||

Some kind of join is probably a good idea since I want to join matching rows as well as non matching rows from both tables. Maybe, a full join would be good but I don't want duplicates. Please see my example of the output. What do you think?

Thanks for your help!

Table 1 - Sample Data Column1a Column2a Column3a 1 T1C2R1 T1C3R1 2 T1C2R2 T1C3R2 3 T1C2R3 T1C3R3 5 T1C2R5 T1C3R5 Table 2 - Sample Data Column1b Column2b Column3b 4 T2C2R4 T2C3R4 2 T2C2R2 T2C3R2 3 T2C2R3 T2C3R3 6 T2C2R6 T2C3R6 Ouptput: Column1a Column2a Column3a Column1b Column2b Column3b 1 T1C2R1 T1C3R1 NULL NULL NULL 2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2 3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3 NULL NULL NULL 4 T2C2R4 T2C3R4 5 T1C2R5 T1C3R5 NULL NULL NULL NULL NULL NULL 6 T2C2R6 T2C3R6

|||

Yes, a full join should work for you.

SELECT a.Column1a, a.Column2a, a.Column3a,

b.Column1b, b.Column2b, b.Column3b

FROM Table1 a FULL JOIN Table2 b ON (a.Column1a = b.Column1b)

There should not be any duplicates in the result set.

COM+ Applications hanging

I have two ATL Dlls that have multiple interfaces and run under two COM+
Applications. Each DLL accesses SQL Server db via ADO calls and one of
the components also interacts with a 3rd party report server system.
We run into intermittent issues with the COM+ Application hanging. Our
client application will go into the tank and checking Component Services
will show that the max call time is very high and just keeps climbing.
Recycling the COM+ Application will typically fix the problem as it clears
out all memory, connections etc.
I'm struggling to figure out if our components have a bug in them or if
there is something flakey about COM+ Applications and perhaps DB Connection
Pooling.
What would cause one instance to hang and then block all other calls to a
COM+ Application?
Are there any good tools that can hack inside the low-level calls and show
what's causing the bottleneck?
Any help would be greatly appreciated. This is driving me nuts as it's
like finding a needle in a haystack.
Thanks
Eric
"Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> wrote in message
news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
>I have two ATL Dlls that have multiple interfaces and run under two COM+
> Applications. Each DLL accesses SQL Server db via ADO calls and one of
> the components also interacts with a 3rd party report server system.
> We run into intermittent issues with the COM+ Application hanging. Our
> client application will go into the tank and checking Component Services
> will show that the max call time is very high and just keeps climbing.
> Recycling the COM+ Application will typically fix the problem as it clears
> out all memory, connections etc.
> I'm struggling to figure out if our components have a bug in them or if
> there is something flakey about COM+ Applications and perhaps DB
> Connection
> Pooling.
This is very typical of a buggy COM+ DLL which fails to return for whatever
reason.

> What would cause one instance to hang and then block all other calls to a
> COM+ Application?
>
There are a gazillion reasons. There is simply insufficient information
provided to hazard a guess what is responsible in your case.

> Are there any good tools that can hack inside the low-level calls and show
> what's causing the bottleneck?
>
Having gone down this road several times myself, I've simply used standard
debugging strategies. In my design, I have built-in tracing designed as part
of the COM+ DLL's that can be turned on or off by a simply registry setting.
This produces log messages stored in a log file for analysis in case these
situations arises. This can provide a lot of insight as to what is
happening.
I really can't be more specific given the limited amount of information you
have posted, but hopefully this will keep you from going off on a tangent
and blaming the COM+ infrastructure, when it fact it is really solid (at
least in my experience).
Brian
|||"Brian Muth" <bmuth@.mvps.org> wrote in message
news:#Updhbn1FHA.904@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> "Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> wrote in message
> news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
of[vbcol=seagreen]
clears
> This is very typical of a buggy COM+ DLL which fails to return for
whatever[vbcol=seagreen]
> reason.
a[vbcol=seagreen]
> There are a gazillion reasons. There is simply insufficient information
> provided to hazard a guess what is responsible in your case.
show
> Having gone down this road several times myself, I've simply used standard
> debugging strategies. In my design, I have built-in tracing designed as
part
> of the COM+ DLL's that can be turned on or off by a simply registry
setting.
> This produces log messages stored in a log file for analysis in case these
> situations arises. This can provide a lot of insight as to what is
> happening.
> I really can't be more specific given the limited amount of information
you
> have posted, but hopefully this will keep you from going off on a tangent
> and blaming the COM+ infrastructure, when it fact it is really solid (at
> least in my experience).
> Brian
>
Thanks for the response Brian. I am not necessarily blaming the COM+
infrastructure. Just trying to make sense of this. We have recently put
in a bunch of code to try to handle all error conditions and exceptions and
are logging them. So far nothing shows up in our logs except a sesation of
logging anything until the application is shutdown and restarted.
We have two different dlls written by different people. The commonality is
they use ADO to talk to SQL Server 2000. I can't reproduce the error in
our test environment and it doesn't happen on all of our systems and is
sporadic but very annoying.
I think what I need are tools so that when it happens I can figure out where
it's occuring and why. I also need to understand the subsystem better.
What would cause the whole application to hang if only one instance of the
component had issues? Could there be a problem with SQL Connections?
The database typically looks fine when this occurs so I don't think it's a
db call that is hanging in SQL.
|||>
> What would cause the whole application to hang if only one instance of the
> component had issues?
You mean you want an example? Ok, if the COM+ object requires a transaction,
then all it takes is for one object to fail or hang, and all subsequent
calls are blocked.
Could there be a problem with SQL Connections?
What sort of problem are you thinking of?
Brian
|||attach a debugger to the hang process and check all thread call
stack should be a fast way to check which thread makes a blocking call.
if this issue has a considerable business impact, i suggest you
submit a service request to Microsoft PSS to ask them for assistance.
"Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> д? news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
I have two ATL Dlls that have multiple interfaces and run under two COM+
Applications. Each DLL accesses SQL Server db via ADO calls and one of
the components also interacts with a 3rd party report server system.
We run into intermittent issues with the COM+ Application hanging. Our
client application will go into the tank and checking Component Services
will show that the max call time is very high and just keeps climbing.
Recycling the COM+ Application will typically fix the problem as it clears
out all memory, connections etc.
I'm struggling to figure out if our components have a bug in them or if
there is something flakey about COM+ Applications and perhaps DB Connection
Pooling.
What would cause one instance to hang and then block all other calls to a
COM+ Application?
Are there any good tools that can hack inside the low-level calls and show
what's causing the bottleneck?
Any help would be greatly appreciated. This is driving me nuts as it's
like finding a needle in a haystack.
Thanks
Eric
|||John, thanks for the response. I probably will be calling MS. I'm not
sure how to attach a debugger in this production environment. Are you
referring to a stand-alone debugging tool that'll work outside of the
development environment.
"John" <lanxiaowei@.hotmail.com> wrote in message
news:OdXBhw#1FHA.2008@.TK2MSFTNGP10.phx.gbl...
attach a debugger to the hang process and check all thread call
stack should be a fast way to check which thread makes a blocking call.
if this issue has a considerable business impact, i suggest you
submit a service request to Microsoft PSS to ask them for assistance.
"Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> д?
news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
I have two ATL Dlls that have multiple interfaces and run under two COM+
Applications. Each DLL accesses SQL Server db via ADO calls and one of
the components also interacts with a 3rd party report server system.
We run into intermittent issues with the COM+ Application hanging. Our
client application will go into the tank and checking Component Services
will show that the max call time is very high and just keeps climbing.
Recycling the COM+ Application will typically fix the problem as it clears
out all memory, connections etc.
I'm struggling to figure out if our components have a bug in them or if
there is something flakey about COM+ Applications and perhaps DB
Connection
Pooling.
What would cause one instance to hang and then block all other calls to a
COM+ Application?
Are there any good tools that can hack inside the low-level calls and show
what's causing the bottleneck?
Any help would be greatly appreciated. This is driving me nuts as it's
like finding a needle in a haystack.
Thanks
Eric
|||"Brian Muth" <bmuth@.mvps.org> wrote in message
news:O2bKUOp1FHA.1032@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
the
> You mean you want an example? Ok, if the COM+ object requires a
transaction,
> then all it takes is for one object to fail or hang, and all subsequent
> calls are blocked.
>
But that would only be from the same client, correct? If a different
client with a different handle to the interface makes a call shouldn't it
have it's own process flow?

> Could there be a problem with SQL Connections?
> What sort of problem are you thinking of?
>
Just wondering if there could be some sort of blocking or threading issue
that would cause all SQL connections (since there is pooling going on) to be
blocked so any calls made to the components would "hang" because they are
primarliy doing db calls via ADO.
|||Response inline...
Michael D. Long
"Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> wrote in message
news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
> What would cause one instance to hang and then block all other calls to a
> COM+ Application?
>
When operating against the DBMS at Read Serializable isolation, a long
running transaction that acquires an exclusive lock on a single data row
will block all other connections accessing the same data row. For that
reason, when developing COM+ applications the developer needs to be
intimately familiar with database locking behavior and data usage patterns.
I saw one guy try to avoid a scalability issue by creating a connection
outside of the scope of the distributed transaction in an effort to improve
performance on a system designed by another team that was bottlenecked at 8
concurrent users. This approach managed to "intermittently" deadlock the
entire application, because the non-transactional connection was contending
with the connection enlisted in the distributed transaction for the same
data row - and his internal recovery logic kept retrying until the object
was terminated.

> Are there any good tools that can hack inside the low-level calls and show
> what's causing the bottleneck?
>
If this is a data related bottleneck you can run a trace of the activity. A
thorough design review wouldn't hurt - this should include validation of the
data access patterns as well as line by line source code walk through.
|||> When operating against the DBMS at Read Serializable isolation, a long
> running transaction that acquires an exclusive lock on a single data row
> will block all other connections accessing the same data row. For that
> reason, when developing COM+ applications the developer needs to be
> intimately familiar with database locking behavior and data usage
patterns.
> I saw one guy try to avoid a scalability issue by creating a connection
> outside of the scope of the distributed transaction in an effort to
improve
> performance on a system designed by another team that was bottlenecked at
8
> concurrent users. This approach managed to "intermittently" deadlock the
> entire application, because the non-transactional connection was
contending
> with the connection enlisted in the distributed transaction for the same
> data row - and his internal recovery logic kept retrying until the object
> was terminated.
Michael would this be detectable from looking at the SQL Server database or
could it appear quiet on the db end with the problem lying wihtin the ADO
calls in the component? I ask because I know that SQL server isn't locking
according to our database team when this happens.
[vbcol=seagreen]
show
> If this is a data related bottleneck you can run a trace of the activity.
A
> thorough design review wouldn't hurt - this should include validation of
the
> data access patterns as well as line by line source code walk through.
>
Thanks for the suggestions.

COM+ Applications hanging

I have two ATL Dlls that have multiple interfaces and run under two COM+
Applications. Each DLL accesses SQL Server db via ADO calls and one of
the components also interacts with a 3rd party report server system.
We run into intermittent issues with the COM+ Application hanging. Our
client application will go into the tank and checking Component Services
will show that the max call time is very high and just keeps climbing.
Recycling the COM+ Application will typically fix the problem as it clears
out all memory, connections etc.
I'm struggling to figure out if our components have a bug in them or if
there is something flakey about COM+ Applications and perhaps DB Connection
Pooling.
What would cause one instance to hang and then block all other calls to a
COM+ Application?
Are there any good tools that can hack inside the low-level calls and show
what's causing the bottleneck?
Any help would be greatly appreciated. This is driving me nuts as it's
like finding a needle in a haystack.
Thanks
Eric"Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> wrote in message
news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
>I have two ATL Dlls that have multiple interfaces and run under two COM+
> Applications. Each DLL accesses SQL Server db via ADO calls and one of
> the components also interacts with a 3rd party report server system.
> We run into intermittent issues with the COM+ Application hanging. Our
> client application will go into the tank and checking Component Services
> will show that the max call time is very high and just keeps climbing.
> Recycling the COM+ Application will typically fix the problem as it clears
> out all memory, connections etc.
> I'm struggling to figure out if our components have a bug in them or if
> there is something flakey about COM+ Applications and perhaps DB
> Connection
> Pooling.
This is very typical of a buggy COM+ DLL which fails to return for whatever
reason.

> What would cause one instance to hang and then block all other calls to a
> COM+ Application?
>
There are a gazillion reasons. There is simply insufficient information
provided to hazard a guess what is responsible in your case.

> Are there any good tools that can hack inside the low-level calls and show
> what's causing the bottleneck?
>
Having gone down this road several times myself, I've simply used standard
debugging strategies. In my design, I have built-in tracing designed as part
of the COM+ DLL's that can be turned on or off by a simply registry setting.
This produces log messages stored in a log file for analysis in case these
situations arises. This can provide a lot of insight as to what is
happening.
I really can't be more specific given the limited amount of information you
have posted, but hopefully this will keep you from going off on a tangent
and blaming the COM+ infrastructure, when it fact it is really solid (at
least in my experience).
Brian|||"Brian Muth" <bmuth@.mvps.org> wrote in message
news:#Updhbn1FHA.904@.tk2msftngp13.phx.gbl...
> "Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> wrote in message
> news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
of[vbcol=seagreen]
clears[vbcol=seagreen]
> This is very typical of a buggy COM+ DLL which fails to return for
whatever
> reason.
>
a[vbcol=seagreen]
> There are a gazillion reasons. There is simply insufficient information
> provided to hazard a guess what is responsible in your case.
>
show[vbcol=seagreen]
> Having gone down this road several times myself, I've simply used standard
> debugging strategies. In my design, I have built-in tracing designed as
part
> of the COM+ DLL's that can be turned on or off by a simply registry
setting.
> This produces log messages stored in a log file for analysis in case these
> situations arises. This can provide a lot of insight as to what is
> happening.
> I really can't be more specific given the limited amount of information
you
> have posted, but hopefully this will keep you from going off on a tangent
> and blaming the COM+ infrastructure, when it fact it is really solid (at
> least in my experience).
> Brian
>
Thanks for the response Brian. I am not necessarily blaming the COM+
infrastructure. Just trying to make sense of this. We have recently put
in a bunch of code to try to handle all error conditions and exceptions and
are logging them. So far nothing shows up in our logs except a sesation of
logging anything until the application is shutdown and restarted.
We have two different dlls written by different people. The commonality is
they use ADO to talk to SQL Server 2000. I can't reproduce the error in
our test environment and it doesn't happen on all of our systems and is
sporadic but very annoying.
I think what I need are tools so that when it happens I can figure out where
it's occuring and why. I also need to understand the subsystem better.
What would cause the whole application to hang if only one instance of the
component had issues? Could there be a problem with SQL Connections?
The database typically looks fine when this occurs so I don't think it's a
db call that is hanging in SQL.|||>
> What would cause the whole application to hang if only one instance of the
> component had issues?
You mean you want an example? Ok, if the COM+ object requires a transaction,
then all it takes is for one object to fail or hang, and all subsequent
calls are blocked.
Could there be a problem with SQL Connections?
What sort of problem are you thinking of?
Brian|||attach a debugger to the hang process and check all thread call
stack should be a fast way to check which thread makes a blocking call.
if this issue has a considerable business impact, i suggest you
submit a service request to Microsoft PSS to ask them for assistance.
"Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> д? news:u%23op5
2m1FHA.460@.TK2MSFTNGP15.phx.gbl...
I have two ATL Dlls that have multiple interfaces and run under two COM+
Applications. Each DLL accesses SQL Server db via ADO calls and one of
the components also interacts with a 3rd party report server system.
We run into intermittent issues with the COM+ Application hanging. Our
client application will go into the tank and checking Component Services
will show that the max call time is very high and just keeps climbing.
Recycling the COM+ Application will typically fix the problem as it clears
out all memory, connections etc.
I'm struggling to figure out if our components have a bug in them or if
there is something flakey about COM+ Applications and perhaps DB Connection
Pooling.
What would cause one instance to hang and then block all other calls to a
COM+ Application?
Are there any good tools that can hack inside the low-level calls and show
what's causing the bottleneck?
Any help would be greatly appreciated. This is driving me nuts as it's
like finding a needle in a haystack.
Thanks
Eric|||John, thanks for the response. I probably will be calling MS. I'm not
sure how to attach a debugger in this production environment. Are you
referring to a stand-alone debugging tool that'll work outside of the
development environment.
"John" <lanxiaowei@.hotmail.com> wrote in message
news:OdXBhw#1FHA.2008@.TK2MSFTNGP10.phx.gbl...
attach a debugger to the hang process and check all thread call
stack should be a fast way to check which thread makes a blocking call.
if this issue has a considerable business impact, i suggest you
submit a service request to Microsoft PSS to ask them for assistance.
"Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> д?
news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
I have two ATL Dlls that have multiple interfaces and run under two COM+
Applications. Each DLL accesses SQL Server db via ADO calls and one of
the components also interacts with a 3rd party report server system.
We run into intermittent issues with the COM+ Application hanging. Our
client application will go into the tank and checking Component Services
will show that the max call time is very high and just keeps climbing.
Recycling the COM+ Application will typically fix the problem as it clears
out all memory, connections etc.
I'm struggling to figure out if our components have a bug in them or if
there is something flakey about COM+ Applications and perhaps DB
Connection
Pooling.
What would cause one instance to hang and then block all other calls to a
COM+ Application?
Are there any good tools that can hack inside the low-level calls and show
what's causing the bottleneck?
Any help would be greatly appreciated. This is driving me nuts as it's
like finding a needle in a haystack.
Thanks
Eric|||"Brian Muth" <bmuth@.mvps.org> wrote in message
news:O2bKUOp1FHA.1032@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
> You mean you want an example? Ok, if the COM+ object requires a
transaction,
> then all it takes is for one object to fail or hang, and all subsequent
> calls are blocked.
>
But that would only be from the same client, correct? If a different
client with a different handle to the interface makes a call shouldn't it
have it's own process flow?

> Could there be a problem with SQL Connections?
> What sort of problem are you thinking of?
>
Just wondering if there could be some sort of blocking or threading issue
that would cause all SQL connections (since there is pooling going on) to be
blocked so any calls made to the components would "hang" because they are
primarliy doing db calls via ADO.|||Response inline...
Michael D. Long
"Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> wrote in message
news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
> What would cause one instance to hang and then block all other calls to a
> COM+ Application?
>
When operating against the DBMS at Read Serializable isolation, a long
running transaction that acquires an exclusive lock on a single data row
will block all other connections accessing the same data row. For that
reason, when developing COM+ applications the developer needs to be
intimately familiar with database locking behavior and data usage patterns.
I saw one guy try to avoid a scalability issue by creating a connection
outside of the scope of the distributed transaction in an effort to improve
performance on a system designed by another team that was bottlenecked at 8
concurrent users. This approach managed to "intermittently" deadlock the
entire application, because the non-transactional connection was contending
with the connection enlisted in the distributed transaction for the same
data row - and his internal recovery logic kept retrying until the object
was terminated.

> Are there any good tools that can hack inside the low-level calls and show
> what's causing the bottleneck?
>
If this is a data related bottleneck you can run a trace of the activity. A
thorough design review wouldn't hurt - this should include validation of the
data access patterns as well as line by line source code walk through.|||> When operating against the DBMS at Read Serializable isolation, a long
> running transaction that acquires an exclusive lock on a single data row
> will block all other connections accessing the same data row. For that
> reason, when developing COM+ applications the developer needs to be
> intimately familiar with database locking behavior and data usage
patterns.
> I saw one guy try to avoid a scalability issue by creating a connection
> outside of the scope of the distributed transaction in an effort to
improve
> performance on a system designed by another team that was bottlenecked at
8
> concurrent users. This approach managed to "intermittently" deadlock the
> entire application, because the non-transactional connection was
contending
> with the connection enlisted in the distributed transaction for the same
> data row - and his internal recovery logic kept retrying until the object
> was terminated.
Michael would this be detectable from looking at the SQL Server database or
could it appear quiet on the db end with the problem lying wihtin the ADO
calls in the component? I ask because I know that SQL server isn't locking
according to our database team when this happens.

show[vbcol=seagreen]
> If this is a data related bottleneck you can run a trace of the activity.
A
> thorough design review wouldn't hurt - this should include validation of
the
> data access patterns as well as line by line source code walk through.
>
Thanks for the suggestions.

COM+ Applications hanging

I have two ATL Dlls that have multiple interfaces and run under two COM+
Applications. Each DLL accesses SQL Server db via ADO calls and one of
the components also interacts with a 3rd party report server system.
We run into intermittent issues with the COM+ Application hanging. Our
client application will go into the tank and checking Component Services
will show that the max call time is very high and just keeps climbing.
Recycling the COM+ Application will typically fix the problem as it clears
out all memory, connections etc.
I'm struggling to figure out if our components have a bug in them or if
there is something flakey about COM+ Applications and perhaps DB Connection
Pooling.
What would cause one instance to hang and then block all other calls to a
COM+ Application?
Are there any good tools that can hack inside the low-level calls and show
what's causing the bottleneck?
Any help would be greatly appreciated. This is driving me nuts as it's
like finding a needle in a haystack.
Thanks
Eric"Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> wrote in message
news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
>I have two ATL Dlls that have multiple interfaces and run under two COM+
> Applications. Each DLL accesses SQL Server db via ADO calls and one of
> the components also interacts with a 3rd party report server system.
> We run into intermittent issues with the COM+ Application hanging. Our
> client application will go into the tank and checking Component Services
> will show that the max call time is very high and just keeps climbing.
> Recycling the COM+ Application will typically fix the problem as it clears
> out all memory, connections etc.
> I'm struggling to figure out if our components have a bug in them or if
> there is something flakey about COM+ Applications and perhaps DB
> Connection
> Pooling.
This is very typical of a buggy COM+ DLL which fails to return for whatever
reason.

> What would cause one instance to hang and then block all other calls to a
> COM+ Application?
>
There are a gazillion reasons. There is simply insufficient information
provided to hazard a guess what is responsible in your case.

> Are there any good tools that can hack inside the low-level calls and show
> what's causing the bottleneck?
>
Having gone down this road several times myself, I've simply used standard
debugging strategies. In my design, I have built-in tracing designed as part
of the COM+ DLL's that can be turned on or off by a simply registry setting.
This produces log messages stored in a log file for analysis in case these
situations arises. This can provide a lot of insight as to what is
happening.
I really can't be more specific given the limited amount of information you
have posted, but hopefully this will keep you from going off on a tangent
and blaming the COM+ infrastructure, when it fact it is really solid (at
least in my experience).
Brian|||"Brian Muth" <bmuth@.mvps.org> wrote in message
news:#Updhbn1FHA.904@.tk2msftngp13.phx.gbl...
> "Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> wrote in message
> news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
of
clears
> This is very typical of a buggy COM+ DLL which fails to return for
whatever
> reason.
>
a
> There are a gazillion reasons. There is simply insufficient information
> provided to hazard a guess what is responsible in your case.
>
show
> Having gone down this road several times myself, I've simply used standard
> debugging strategies. In my design, I have built-in tracing designed as
part
> of the COM+ DLL's that can be turned on or off by a simply registry
setting.
> This produces log messages stored in a log file for analysis in case these
> situations arises. This can provide a lot of insight as to what is
> happening.
> I really can't be more specific given the limited amount of information
you
> have posted, but hopefully this will keep you from going off on a tangent
> and blaming the COM+ infrastructure, when it fact it is really solid (at
> least in my experience).
> Brian
>
Thanks for the response Brian. I am not necessarily blaming the COM+
infrastructure. Just trying to make sense of this. We have recently put
in a bunch of code to try to handle all error conditions and exceptions and
are logging them. So far nothing shows up in our logs except a sesation of
logging anything until the application is shutdown and restarted.
We have two different dlls written by different people. The commonality is
they use ADO to talk to SQL Server 2000. I can't reproduce the error in
our test environment and it doesn't happen on all of our systems and is
sporadic but very annoying.
I think what I need are tools so that when it happens I can figure out where
it's occuring and why. I also need to understand the subsystem better.
What would cause the whole application to hang if only one instance of the
component had issues? Could there be a problem with SQL Connections?
The database typically looks fine when this occurs so I don't think it's a
db call that is hanging in SQL.|||>
> What would cause the whole application to hang if only one instance of the
> component had issues?
You mean you want an example? Ok, if the COM+ object requires a transaction,
then all it takes is for one object to fail or hang, and all subsequent
calls are blocked.
Could there be a problem with SQL Connections?
What sort of problem are you thinking of?
Brian|||attach a debugger to the hang process and check all thread call
stack should be a fast way to check which thread makes a blocking call.
if this issue has a considerable business impact, i suggest you
submit a service request to Microsoft PSS to ask them for assistance.
"Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> д? news:u%23op5
2m1FHA.460@.TK2MSFTNGP15.phx.gbl...
I have two ATL Dlls that have multiple interfaces and run under two COM+
Applications. Each DLL accesses SQL Server db via ADO calls and one of
the components also interacts with a 3rd party report server system.
We run into intermittent issues with the COM+ Application hanging. Our
client application will go into the tank and checking Component Services
will show that the max call time is very high and just keeps climbing.
Recycling the COM+ Application will typically fix the problem as it clears
out all memory, connections etc.
I'm struggling to figure out if our components have a bug in them or if
there is something flakey about COM+ Applications and perhaps DB Connection
Pooling.
What would cause one instance to hang and then block all other calls to a
COM+ Application?
Are there any good tools that can hack inside the low-level calls and show
what's causing the bottleneck?
Any help would be greatly appreciated. This is driving me nuts as it's
like finding a needle in a haystack.
Thanks
Eric|||John, thanks for the response. I probably will be calling MS. I'm not
sure how to attach a debugger in this production environment. Are you
referring to a stand-alone debugging tool that'll work outside of the
development environment.
"John" <lanxiaowei@.hotmail.com> wrote in message
news:OdXBhw#1FHA.2008@.TK2MSFTNGP10.phx.gbl...
attach a debugger to the hang process and check all thread call
stack should be a fast way to check which thread makes a blocking call.
if this issue has a considerable business impact, i suggest you
submit a service request to Microsoft PSS to ask them for assistance.
"Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> д?
news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
I have two ATL Dlls that have multiple interfaces and run under two COM+
Applications. Each DLL accesses SQL Server db via ADO calls and one of
the components also interacts with a 3rd party report server system.
We run into intermittent issues with the COM+ Application hanging. Our
client application will go into the tank and checking Component Services
will show that the max call time is very high and just keeps climbing.
Recycling the COM+ Application will typically fix the problem as it clears
out all memory, connections etc.
I'm struggling to figure out if our components have a bug in them or if
there is something flakey about COM+ Applications and perhaps DB
Connection
Pooling.
What would cause one instance to hang and then block all other calls to a
COM+ Application?
Are there any good tools that can hack inside the low-level calls and show
what's causing the bottleneck?
Any help would be greatly appreciated. This is driving me nuts as it's
like finding a needle in a haystack.
Thanks
Eric|||"Brian Muth" <bmuth@.mvps.org> wrote in message
news:O2bKUOp1FHA.1032@.TK2MSFTNGP12.phx.gbl...
the
> You mean you want an example? Ok, if the COM+ object requires a
transaction,
> then all it takes is for one object to fail or hang, and all subsequent
> calls are blocked.
>
But that would only be from the same client, correct? If a different
client with a different handle to the interface makes a call shouldn't it
have it's own process flow?

> Could there be a problem with SQL Connections?
> What sort of problem are you thinking of?
>
Just wondering if there could be some sort of blocking or threading issue
that would cause all SQL connections (since there is pooling going on) to be
blocked so any calls made to the components would "hang" because they are
primarliy doing db calls via ADO.|||"Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> wrote in message
news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
>I have two ATL Dlls that have multiple interfaces and run under two COM+
> Applications. Each DLL accesses SQL Server db via ADO calls and one of
> the components also interacts with a 3rd party report server system.
> We run into intermittent issues with the COM+ Application hanging. Our
> client application will go into the tank and checking Component Services
> will show that the max call time is very high and just keeps climbing.
> Recycling the COM+ Application will typically fix the problem as it clears
> out all memory, connections etc.
> I'm struggling to figure out if our components have a bug in them or if
> there is something flakey about COM+ Applications and perhaps DB
> Connection
> Pooling.
> What would cause one instance to hang and then block all other calls to a
> COM+ Application?
> Are there any good tools that can hack inside the low-level calls and show
> what's causing the bottleneck?
> Any help would be greatly appreciated. This is driving me nuts as it's
> like finding a needle in a haystack.
Have you considered the possibility of a SQL deadlock? Execute sp_who2 when
the condition occurs again, to see if your [SQL] process is being blocked by
another. If that is the case, you can use Enterprise Manager
(Management->Current Activity->Process Info) to investigate further. From
there, by double-clicking a process item, you can [usually] view the last
SQL statement executed. From there you may or may not have your work cut
out for you, depending on the circumstances, of course.
-Mark
[non-relevant cross-posts removed]

> Thanks
> Eric
>|||Response inline...
Michael D. Long
"Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> wrote in message
news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
> What would cause one instance to hang and then block all other calls to a
> COM+ Application?
>
When operating against the DBMS at Read Serializable isolation, a long
running transaction that acquires an exclusive lock on a single data row
will block all other connections accessing the same data row. For that
reason, when developing COM+ applications the developer needs to be
intimately familiar with database locking behavior and data usage patterns.
I saw one guy try to avoid a scalability issue by creating a connection
outside of the scope of the distributed transaction in an effort to improve
performance on a system designed by another team that was bottlenecked at 8
concurrent users. This approach managed to "intermittently" deadlock the
entire application, because the non-transactional connection was contending
with the connection enlisted in the distributed transaction for the same
data row - and his internal recovery logic kept retrying until the object
was terminated.

> Are there any good tools that can hack inside the low-level calls and show
> what's causing the bottleneck?
>
If this is a data related bottleneck you can run a trace of the activity. A
thorough design review wouldn't hurt - this should include validation of the
data access patterns as well as line by line source code walk through.|||"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:#InvFiS2FHA.3244@.tk2msftngp13.phx.gbl...
> "Eric Margheim" <NOSPAM***eric@.prism-grp.com***NOSPAM> wrote in message
> news:u%23op52m1FHA.460@.TK2MSFTNGP15.phx.gbl...
of
clears
a
show
> Have you considered the possibility of a SQL deadlock? Execute sp_who2
when
> the condition occurs again, to see if your [SQL] process is being blocked
by
> another. If that is the case, you can use Enterprise Manager
> (Management->Current Activity->Process Info) to investigate further. From
> there, by double-clicking a process item, you can [usually] view the last
> SQL statement executed. From there you may or may not have your work cut
> out for you, depending on the circumstances, of course.
>
Thanks Mark. Yes I have considered that. Whenever this occurs I have the
db team look at SQL. Sometimes our problem is with database load/locking
but in the cases I'm trying to debug the database is quiet.

COM access to SERVERPROPERTY values

Is it possible to get the properties retrievable with T-SQL
SERVERPROPERTY via COM access instead? Do you know some sqlserver COM
object that can provide this kind of information (e.g. LicenseType or
NumLicenses)? Any Ideas?You could use SQL DMO object model. See SQL Server Books Online for more
info. See SQLServer2 object for a start.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Holger" <atlan@.tournedos.de> wrote in message
news:1124040877.686226.191970@.g43g2000cwa.googlegroups.com...
> Is it possible to get the properties retrievable with T-SQL
> SERVERPROPERTY via COM access instead? Do you know some sqlserver COM
> object that can provide this kind of information (e.g. LicenseType or
> NumLicenses)? Any Ideas?
>|||Thank you for your reply but in COM programming it is always good to
read the manual first. The object SQLServer2 does not provide access to
the properties LicenseType or NumLicenses exposed by SERVERPROPERTY.
These properties are of special interest to me. Any further object you
can recommend?|||Another approach is simply the registry to get these values
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\M
icrosoft SQL
Server\80\MSSQLLicenseInfo\MSSQL8.00]
"ConcurrentLimit"=dword:00000006
"Mode"=dword:00000002
Mode = 2 means PerProcessor
Mode != 2 means PerSeat
ConcurrentLimit = Number of Seats OR Processors
Just to complete this topic,
Holger

Columns & rowns interchage on string the data

Hi,

Is it possible to interchange column & row data from the t-sql on
storing into a file?

Thanks

*** Sent via Developersdex http://www.developersdex.com ***Simon,

Thanks for your reply. I woule like the following output data

col1 col2 col3 ......

row1 r1c1 r1c2 c1c3 ...

row2 r2c1 r2c2 r2c3 ....

row3 r3c1 r3c2 r3c3 ......

..
..
..

to be output like

row1 row2 row3 ......

col1 r1c1 r2c1 r3c1

col2 r1c2 r2c2 r3c2

col3 r1c3 r2c3 r3c3
..
..
..

Thanks

*** Sent via Developersdex http://www.developersdex.com ***|||I'm not sure I understand your question, but I think you're asking if
you can export a crosstab report to a file? If so, a reporting tool is
probably the best general solution (MSSQL Reporting Services, Business
Objects etc), but you could create your own qrosstab queries:

http://www.aspfaq.com/show.asp?id=2462

To export the results to a file, you could use osql.exe, bcp.exe or
DTS.

Simon|||Here's an example:

CREATE TABLE foo (foo_key INTEGER PRIMARY KEY, x INTEGER NOT NULL, y
INTEGER NOT NULL, z INTEGER NOT NULL) ;

INSERT INTO foo (foo_key, x, y, z)
SELECT 1,10,11,12 UNION ALL
SELECT 2,20,21,22 UNION ALL
SELECT 3,30,31,32 ;

SELECT foo_key, x, y, z
FROM foo ;

SELECT 'x' AS col,
MAX(CASE WHEN foo_key = 1 THEN x END) AS row1,
MAX(CASE WHEN foo_key = 2 THEN x END) AS row2,
MAX(CASE WHEN foo_key = 3 THEN x END) AS row3
FROM foo
UNION ALL
SELECT 'y',
MAX(CASE WHEN foo_key = 1 THEN y END),
MAX(CASE WHEN foo_key = 2 THEN y END),
MAX(CASE WHEN foo_key = 3 THEN y END)
FROM foo
UNION ALL
SELECT 'z',
MAX(CASE WHEN foo_key = 1 THEN z END),
MAX(CASE WHEN foo_key = 2 THEN z END),
MAX(CASE WHEN foo_key = 3 THEN z END)
FROM foo ;

--
David Portas
SQL Server MVP
--

Column Encryption via stored procedure

Hi
http://blogs.msdn.com/lcris/--Enscrypt
"MartinaL" <MartinaL@.discussions.microsoft.com> wrote in message
news:E88D0208-1BB5-4834-91BA-F7A332A827EB@.microsoft.com...
>I have a registration process on a website which sends in the
> username/password etc via a stored procedure into SQL 2005.
> I want to encrypt the password on the way in in the stored procedure. This
> is the code that I am trying to do this with;
> ALTER PROCEDURE [dbo].[AdminsAdd]
> (
> @.AdminUsername varchar(100),
> @.AdminPassword varchar(100),
> @.AdminName varchar(100)
> )
> AS
> OPEN SYMMETRIC KEY SSN_Key_02
> DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
> INSERT INTO Admins
> (
> AdminUsername,
> AdminPassword,
> AdminName
> )
> VALUES
> (
> @.AdminUsername,
> EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
> @.AdminName
> )
> I have used similar code to encrypt all of the current passwords that are
> in
> the table and it works fine. And I have another stored procedure that
> checks
> people's details when they are login in and the decrypt function works
> fine
> in that procedure.
> What am I doing wrong in this Stored Proc?I have a registration process on a website which sends in the
username/password etc via a stored procedure into SQL 2005.
I want to encrypt the password on the way in in the stored procedure. This
is the code that I am trying to do this with;
ALTER PROCEDURE [dbo].[AdminsAdd]
(
@.AdminUsername varchar(100),
@.AdminPassword varchar(100),
@.AdminName varchar(100)
)
AS
OPEN SYMMETRIC KEY SSN_Key_02
DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
INSERT INTO Admins
(
AdminUsername,
AdminPassword,
AdminName
)
VALUES
(
@.AdminUsername,
EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
@.AdminName
)
I have used similar code to encrypt all of the current passwords that are in
the table and it works fine. And I have another stored procedure that checks
people's details when they are login in and the decrypt function works fine
in that procedure.
What am I doing wrong in this Stored Proc?|||Hi
http://blogs.msdn.com/lcris/--Enscrypt
"MartinaL" <MartinaL@.discussions.microsoft.com> wrote in message
news:E88D0208-1BB5-4834-91BA-F7A332A827EB@.microsoft.com...
>I have a registration process on a website which sends in the
> username/password etc via a stored procedure into SQL 2005.
> I want to encrypt the password on the way in in the stored procedure. This
> is the code that I am trying to do this with;
> ALTER PROCEDURE [dbo].[AdminsAdd]
> (
> @.AdminUsername varchar(100),
> @.AdminPassword varchar(100),
> @.AdminName varchar(100)
> )
> AS
> OPEN SYMMETRIC KEY SSN_Key_02
> DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
> INSERT INTO Admins
> (
> AdminUsername,
> AdminPassword,
> AdminName
> )
> VALUES
> (
> @.AdminUsername,
> EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
> @.AdminName
> )
> I have used similar code to encrypt all of the current passwords that are
> in
> the table and it works fine. And I have another stored procedure that
> checks
> people's details when they are login in and the decrypt function works
> fine
> in that procedure.
> What am I doing wrong in this Stored Proc?|||What goes wrong with your code? Do you get an error message, does it not
encrypt the passwords properly?
One issue is that you don't close the key in the procedure. You should close
the key after inserting the data. Otherwise, it will remain open after the
procedure call completes, and it will stay open until the session will be
terminated.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"MartinaL" <MartinaL@.discussions.microsoft.com> wrote in message
news:E88D0208-1BB5-4834-91BA-F7A332A827EB@.microsoft.com...
>I have a registration process on a website which sends in the
> username/password etc via a stored procedure into SQL 2005.
> I want to encrypt the password on the way in in the stored procedure. This
> is the code that I am trying to do this with;
> ALTER PROCEDURE [dbo].[AdminsAdd]
> (
> @.AdminUsername varchar(100),
> @.AdminPassword varchar(100),
> @.AdminName varchar(100)
> )
> AS
> OPEN SYMMETRIC KEY SSN_Key_02
> DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
> INSERT INTO Admins
> (
> AdminUsername,
> AdminPassword,
> AdminName
> )
> VALUES
> (
> @.AdminUsername,
> EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
> @.AdminName
> )
> I have used similar code to encrypt all of the current passwords that are
> in
> the table and it works fine. And I have another stored procedure that
> checks
> people's details when they are login in and the decrypt function works
> fine
> in that procedure.
> What am I doing wrong in this Stored Proc?|||What goes wrong with your code? Do you get an error message, does it not
encrypt the passwords properly?
One issue is that you don't close the key in the procedure. You should close
the key after inserting the data. Otherwise, it will remain open after the
procedure call completes, and it will stay open until the session will be
terminated.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"MartinaL" <MartinaL@.discussions.microsoft.com> wrote in message
news:E88D0208-1BB5-4834-91BA-F7A332A827EB@.microsoft.com...
>I have a registration process on a website which sends in the
> username/password etc via a stored procedure into SQL 2005.
> I want to encrypt the password on the way in in the stored procedure. This
> is the code that I am trying to do this with;
> ALTER PROCEDURE [dbo].[AdminsAdd]
> (
> @.AdminUsername varchar(100),
> @.AdminPassword varchar(100),
> @.AdminName varchar(100)
> )
> AS
> OPEN SYMMETRIC KEY SSN_Key_02
> DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
> INSERT INTO Admins
> (
> AdminUsername,
> AdminPassword,
> AdminName
> )
> VALUES
> (
> @.AdminUsername,
> EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
> @.AdminName
> )
> I have used similar code to encrypt all of the current passwords that are
> in
> the table and it works fine. And I have another stored procedure that
> checks
> people's details when they are login in and the decrypt function works
> fine
> in that procedure.
> What am I doing wrong in this Stored Proc?|||Also if it's a password, you might consider a one-way hash instead of
encryption.
"MartinaL" <MartinaL@.discussions.microsoft.com> wrote in message
news:E88D0208-1BB5-4834-91BA-F7A332A827EB@.microsoft.com...
>I have a registration process on a website which sends in the
> username/password etc via a stored procedure into SQL 2005.
> I want to encrypt the password on the way in in the stored procedure. This
> is the code that I am trying to do this with;
> ALTER PROCEDURE [dbo].[AdminsAdd]
> (
> @.AdminUsername varchar(100),
> @.AdminPassword varchar(100),
> @.AdminName varchar(100)
> )
> AS
> OPEN SYMMETRIC KEY SSN_Key_02
> DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
> INSERT INTO Admins
> (
> AdminUsername,
> AdminPassword,
> AdminName
> )
> VALUES
> (
> @.AdminUsername,
> EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
> @.AdminName
> )
> I have used similar code to encrypt all of the current passwords that are
> in
> the table and it works fine. And I have another stored procedure that
> checks
> people's details when they are login in and the decrypt function works
> fine
> in that procedure.
> What am I doing wrong in this Stored Proc?|||Also if it's a password, you might consider a one-way hash instead of
encryption.
"MartinaL" <MartinaL@.discussions.microsoft.com> wrote in message
news:E88D0208-1BB5-4834-91BA-F7A332A827EB@.microsoft.com...
>I have a registration process on a website which sends in the
> username/password etc via a stored procedure into SQL 2005.
> I want to encrypt the password on the way in in the stored procedure. This
> is the code that I am trying to do this with;
> ALTER PROCEDURE [dbo].[AdminsAdd]
> (
> @.AdminUsername varchar(100),
> @.AdminPassword varchar(100),
> @.AdminName varchar(100)
> )
> AS
> OPEN SYMMETRIC KEY SSN_Key_02
> DECRYPTION BY CERTIFICATE testEncryptionCertificate2;
> INSERT INTO Admins
> (
> AdminUsername,
> AdminPassword,
> AdminName
> )
> VALUES
> (
> @.AdminUsername,
> EncryptByKey(Key_GUID('SSN_Key_02'), @.AdminPassword),
> @.AdminName
> )
> I have used similar code to encrypt all of the current passwords that are
> in
> the table and it works fine. And I have another stored procedure that
> checks
> people's details when they are login in and the decrypt function works
> fine
> in that procedure.
> What am I doing wrong in this Stored Proc?