implicit txn

Hi ,
wat's exactly implicit transaction being turned on in the
db options ?
does it mean i have to supply a commit txn for the
changes to being written to the db ?
thks & rdgs
Hi,
In this mode you should either execute commit or rollback the transaction.
If you just disconnect all the transaction for that session
will be rolled back automatically.
This is actuall a session wide setting or server wide setting.
Session wide
SET IMPLICIT_TRANSACTIONS ON
Server wide
exec sp_configure N'user options', 2
Thanks
Hari
MCDBA
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:cc2601c48a6a$1ce36e90$a401280a@.phx.gbl...
> Hi ,
> wat's exactly implicit transaction being turned on in the
> db options ?
> does it mean i have to supply a commit txn for the
> changes to being written to the db ?
> thks & rdgs
|||Normally when you wish to have multiple statements grouped together in a
single transaction you must do a
Begin transaction... All work you do becomes part of the same group
transaction until you either commit or rollback the transaction.
Any statements (insert, update, delete) you make OUTSIDE of a begin tran,
commit pair are considered to be a transaction... Each statement is in it's
own transaction.
When you set implicit_transactions on, SQL Behaves differently.
Begin tran is no longer allowed, and almost EVERY statement you do
automatically begins a multi-statement transaction ( instead of each
statement being in it;s own tran.). Everything you do is aggregated into a
group transaction until you commit or rollback...
Most people do NOT use implicit_transactions.
BOL has more information ...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:cc2601c48a6a$1ce36e90$a401280a@.phx.gbl...
> Hi ,
> wat's exactly implicit transaction being turned on in the
> db options ?
> does it mean i have to supply a commit txn for the
> changes to being written to the db ?
> thks & rdgs

implicit txn

Hi ,
wat's exactly implicit transaction being turned on in the
db options ?
does it mean i have to supply a commit txn for the
changes to being written to the db ?
thks & rdgsHi,
In this mode you should either execute commit or rollback the transaction.
If you just disconnect all the transaction for that session
will be rolled back automatically.
This is actuall a session wide setting or server wide setting.
Session wide
SET IMPLICIT_TRANSACTIONS ON
Server wide
--
exec sp_configure N'user options', 2
Thanks
Hari
MCDBA
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:cc2601c48a6a$1ce36e90$a401280a@.phx.gbl...
> Hi ,
> wat's exactly implicit transaction being turned on in the
> db options ?
> does it mean i have to supply a commit txn for the
> changes to being written to the db ?
> thks & rdgs|||Normally when you wish to have multiple statements grouped together in a
single transaction you must do a
Begin transaction... All work you do becomes part of the same group
transaction until you either commit or rollback the transaction.
Any statements (insert, update, delete) you make OUTSIDE of a begin tran,
commit pair are considered to be a transaction... Each statement is in it's
own transaction.
When you set implicit_transactions on, SQL Behaves differently.
Begin tran is no longer allowed, and almost EVERY statement you do
automatically begins a multi-statement transaction ( instead of each
statement being in it;s own tran.). Everything you do is aggregated into a
group transaction until you commit or rollback...
Most people do NOT use implicit_transactions.
BOL has more information ...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:cc2601c48a6a$1ce36e90$a401280a@.phx.gbl...
> Hi ,
> wat's exactly implicit transaction being turned on in the
> db options ?
> does it mean i have to supply a commit txn for the
> changes to being written to the db ?
> thks & rdgs

implicit txn

Hi ,
wat's exactly implicit transaction being turned on in the
db options ?
does it mean i have to supply a commit txn for the
changes to being written to the db ?
thks & rdgsHi,
In this mode you should either execute commit or rollback the transaction.
If you just disconnect all the transaction for that session
will be rolled back automatically.
This is actuall a session wide setting or server wide setting.
Session wide
SET IMPLICIT_TRANSACTIONS ON
Server wide
--
exec sp_configure N'user options', 2
Thanks
Hari
MCDBA
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:cc2601c48a6a$1ce36e90$a401280a@.phx.gbl...
> Hi ,
> wat's exactly implicit transaction being turned on in the
> db options ?
> does it mean i have to supply a commit txn for the
> changes to being written to the db ?
> thks & rdgs|||>--Original Message--
>Hi ,
> wat's exactly implicit transaction being turned on in
the
>db options ?
> does it mean i have to supply a commit txn for the
>changes to being written to the db ?
>thks & rdgs
>.
>|||Normally when you wish to have multiple statements grouped together in a
single transaction you must do a
Begin transaction... All work you do becomes part of the same group
transaction until you either commit or rollback the transaction.
Any statements (insert, update, delete) you make OUTSIDE of a begin tran,
commit pair are considered to be a transaction... Each statement is in it's
own transaction.
When you set implicit_transactions on, SQL Behaves differently.
Begin tran is no longer allowed, and almost EVERY statement you do
automatically begins a multi-statement transaction ( instead of each
statement being in it;s own tran.). Everything you do is aggregated into a
group transaction until you commit or rollback...
Most people do NOT use implicit_transactions.
BOL has more information ...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:cc2601c48a6a$1ce36e90$a401280a@.phx.gbl...
> Hi ,
> wat's exactly implicit transaction being turned on in the
> db options ?
> does it mean i have to supply a commit txn for the
> changes to being written to the db ?
> thks & rdgs

Implicit Transations and Deleted Rows

I have a problem where records a being deleted via implicit transactions (I
can see this from the SQL log). I am not sure I understand implicit
transactions fully but no where in our database or application do we ever
delete these records. Why are they being deleted via implicit transactions?Martin,
What do you mean, you can see this from the SQL Log? What is the exact
message in the sql error log?
Can you run a profiler trace on your SQL Server to see what/who is actually
sending these commands?
Do you have replication set up on this server (there is a thing known as
compensating deletes that sometimes surprises people)?
Set up a profiler trace, and make sure to capture sp:starting/completed,
stmt starting/completed, batch starting/completed, rpc starting/completed.
Make sure to include the columns loginname, hostname, etc. Of course you
will want text data and all the regular columns. If you've never set this up
before and you still have questions, post a message here and we can go
through this together.
Donna
"Martin" wrote:
> I have a problem where records a being deleted via implicit transactions (I
> can see this from the SQL log). I am not sure I understand implicit
> transactions fully but no where in our database or application do we ever
> delete these records. Why are they being deleted via implicit transactions?

Implicit Transations and Deleted Rows

I have a problem where records a being deleted via implicit transactions (I
can see this from the SQL log). I am not sure I understand implicit
transactions fully but no where in our database or application do we ever
delete these records. Why are they being deleted via implicit transactions?Martin,
What do you mean, you can see this from the SQL Log? What is the exact
message in the sql error log?
Can you run a profiler trace on your SQL Server to see what/who is actually
sending these commands?
Do you have replication set up on this server (there is a thing known as
compensating deletes that sometimes surprises people)?
Set up a profiler trace, and make sure to capture sp:starting/completed,
stmt starting/completed, batch starting/completed, rpc starting/completed.
Make sure to include the columns loginname, hostname, etc. Of course you
will want text data and all the regular columns. If you've never set this u
p
before and you still have questions, post a message here and we can go
through this together.
Donna
"Martin" wrote:
[vbcol=seagreen]
> I have a problem where records a being deleted via implicit transactions (
I
> can see this from the SQL log). I am not sure I understand implicit
> transactions fully but no where in our database or application do we ever
> delete these records. Why are they being deleted via implicit transactions?[/vbcol
]

Implicit Transations and Deleted Rows

I have a problem where records a being deleted via implicit transactions (I
can see this from the SQL log). I am not sure I understand implicit
transactions fully but no where in our database or application do we ever
delete these records. Why are they being deleted via implicit transactions?
Martin,
What do you mean, you can see this from the SQL Log? What is the exact
message in the sql error log?
Can you run a profiler trace on your SQL Server to see what/who is actually
sending these commands?
Do you have replication set up on this server (there is a thing known as
compensating deletes that sometimes surprises people)?
Set up a profiler trace, and make sure to capture sp:starting/completed,
stmt starting/completed, batch starting/completed, rpc starting/completed.
Make sure to include the columns loginname, hostname, etc. Of course you
will want text data and all the regular columns. If you've never set this up
before and you still have questions, post a message here and we can go
through this together.
Donna
"Martin" wrote:

> I have a problem where records a being deleted via implicit transactions (I
> can see this from the SQL log). I am not sure I understand implicit
> transactions fully but no where in our database or application do we ever
> delete these records. Why are they being deleted via implicit transactions?

Implicit Transaction mode in SQL Server 2000

Hi all:

I know i can use the sentence SET IMPLICIT_TRANSACTIONS ON in a Stored Procedure to force SQL Server to set the connection into implicit transaction mode.

Have i a sentence or configuration to force all SQL Server connections to implicit transaction mode?

Thanks in advance.

Nope, there is no way to change the default isolation level.

I have requested that this be added into the next release of SQL Server. Here is a link to the feature request https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=263746

|||Moving to appropriate forum from Documentation forum section.

Implicit transaction for a page request?

If I execute multiple SqlCommands in a single .aspx, using a single SqlConnection, are all the commands part of the same transaction?

That is, if I say:

cmd1.CommandText = "UPDATE foo SET myVal = myVal - 1"
cmd2.CommandText = "SELECT myVal FROM foo"
cmd1.Connection = myConn
cmd2.Connection = myConn
myConn.Open()
cmd1.ExecuteNonQuery() ' decrement myVal
intResult = CInt(cmd2.ExecuteScalar()) ' select value of myVal
myConn.Close()

Do the UPDATE and the SELECT happen in the same transaction, or is it possible that someone else would have changed the value of myVal between these two calls?Nope. Each statement has an implicit transaction but to put them both under a single transaction you have to use an explicit transaction.

So yes, data could have changed between the statements.

Don|||Thanks for the clarification. So if I understand you right, this:

cmd.CommandText = "UPDATE ... ; SELECT @.myResult = ... "
cmd.ExecuteNonQuery()

would put them in the same transaction, which is what I want.|||Now I'm on shakier ground. I'm pretty sure that SQL Server still considers them to be separate statements, simply batched together. But not absolutely positive.

Any reason you don't want to just use an explicit transaction?

Don|||No -- I'd like to use an explicit transaction, but I'm not sure how. Can you give me an example of how to do it w/o moving the queries to a stored procedure?

Thanks!|||Well, you're opening yourself up to all kinds of security holes by using dynamic SQL like this. Stored procedures are much better way to go.

But you can either use transactions via dynamic SQL and batch statements, or using the SQLTransaction class in ADO.NET. The example in the .NET docs is pretty clear. For the latter, just new up a SQLTransaction object and use the connection object to begin the transaction. Then call either the Commit or Rollback methods of the transaction object.

But remember that your app won't be very secure unless you take all the steps to close the holes.

Don|||Thanks, Don. What kind of hacks am I risking? I'm using parameters to avoid sql-insertion attacks; are there other risks that come with using SqlCommands?|||Ah, you're using ADO.NET parameters? That wasn't clear from the snippets you've posted, although you had one @.Result showing. You're using parameters to provide the changeable values in the SQL, right?

If that's the case, you're probaly reasonably well protected from SQL injection, since ADO.NET passes the SQL and parameters separately to SQL Server, where they are inserted into the SQL.

Don

Implicit transaction count

Hello, everyone:
I need total transaction count. @.@.TRANCOUNT returens the number of explicit transaction. How about implicit transaction? Thanks a lot.
ZYTHmmm... ?
@.@.trancount (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_8lx0.asp)
SET IMPLICIT_TRANSACTIONS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7mur.asp)

Implicit transaction

Can Implicit Transactions be set for the Data Base and not for every query in
SQL Server? For a Data Base, how do we set that and how do we verify the
existing setting .
Ven
There is not databse option for this, but you do not have to set it for every
statement, you set it for the connection and it remains in effect until the
connection executes a SET IMPLICIT_TRANSACTIONS OFF statement.
AMB
"Ven" wrote:

> Can Implicit Transactions be set for the Data Base and not for every query in
> SQL Server? For a Data Base, how do we set that and how do we verify the
> existing setting .
> --
> Ven
|||Be wary of using this option, your program will remain in transaction state
almost all of the time..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Ven" <Ven@.discussions.microsoft.com> wrote in message
news:7B4AB9B4-8003-4122-AD69-0695985785A7@.microsoft.com...
> Can Implicit Transactions be set for the Data Base and not for every query
in
> SQL Server? For a Data Base, how do we set that and how do we verify the
> existing setting .
> --
> Ven

Implicit transaction

Can Implicit Transactions be set for the Data Base and not for every query i
n
SQL Server? For a Data Base, how do we set that and how do we verify the
existing setting .
--
VenThere is not databse option for this, but you do not have to set it for ever
y
statement, you set it for the connection and it remains in effect until the
connection executes a SET IMPLICIT_TRANSACTIONS OFF statement.
AMB
"Ven" wrote:

> Can Implicit Transactions be set for the Data Base and not for every query
in
> SQL Server? For a Data Base, how do we set that and how do we verify the
> existing setting .
> --
> Ven|||Be wary of using this option, your program will remain in transaction state
almost all of the time..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Ven" <Ven@.discussions.microsoft.com> wrote in message
news:7B4AB9B4-8003-4122-AD69-0695985785A7@.microsoft.com...
> Can Implicit Transactions be set for the Data Base and not for every query
in
> SQL Server? For a Data Base, how do we set that and how do we verify the
> existing setting .
> --
> Ven

Implicit Text to Binary Conversion

/***
Requirement: Insert ascii text into varbinary field allowing SQL to convert
the data implicitly
Problem: The data is encoded as Unicode and is truncated or simply blanked
out after the first 10 characters
The following is valid and works; however, it doesn't solve my problem.
As a result, I've tried several approaches, including creating a cursor and
calling Cast() to convert the data to binary. This appears to work, but
again, stops after the first 10 characters
**/
DECLARE @.docHandle int
DECLARE @.xmlDoc varchar(5000)
SET @.xmlDoc ='
<?xml version="1.0"?>
<f2dbarchive>
<archive_timestamp datetime="2004-Sep-27 12:28:48 AM" />
<archive_test id="1530" />
<!-- tables used in this archive -->
<tables>
<Row txtTablename="tblFinalData" txtKeyName="lngInstanceID"
lngKeyValue="2096" lngRecordCount="4" />
</tables>
<!-- data from the tables -->
<data>
<tblFinalData>
<Row lngDataID="5336" lngInstanceID="2096" binData="10946534210000000000276
Final
35031.16534415632448.030617561162.062056640780.437 87345288720.529611595975413.8801526137533849.47590 9519025.77227602431221.58205664078257.545
0.085 DUST SPOTTER 3 LOF 85.00
NY3849.475909519025.7722760243122.0620566407829311 98.67 32086.86
160 85 31198.67 32086.86
140.48 85 0 0 1656
Y Y34149.809 33025.014 168.247 TOWER @. 5B SOUTH PED
123.21566117 -0.30555699 0.0010055 0.00555699
162.7038151636657.46 2017 Y Y34642.169 33069.594
172.887 MND 5.5B_NEW
147.95450669 -0.39920593 0.02882665 0.34920593 167.814226706570
2019 N N31198.912 32050.403 140.93 GP
8 OBS PT 0 0
-0.000382 0.01695564 0 0 1658 Y
Y35282.726 33023.024 175.67 TOWER @. 6B SOUTH PED
203.64517087 -1.08627593 0.00482913 0.03627593
163.7961106000857.63 2015 N N31892.772 32960.261
153.253 OBS PT 3B SOUTH 0
0 0.00531249 0.11706955 0 0
"
intPackageCount="" booGood="1" txtShotID="" txtStatus="Field" qvTime="0" />
<Row lngDataID="5337" lngInstanceID="2096" binData="10946539640000000000277
Final
34900.43868810932427.234930849677.0883333717911.85 11700763559.4096332145782681.194252534983717.38436 4552516.449301983071536.608333371790
0 1ST LIGHT SPOTTER 3
NY3717.384364552516.449301983071517.08833337179311 98.67
32086.86 160 85 31198.67
32086.86 140.48 85 0 0 1656
Y Y34149.809 33025.014 168.247 TOWER @. 5B SOUTH
PED 128.4489945 34.57777634 0.0010055
0.00555699 829.755965865400 2017 Y Y34642.169
33069.594 172.887 MND 5.5B_NEW
158.30450669 44.65079407 0.02882665 0.34920593
856.898004060910 2019 N N31198.912 32050.403
140.93 GP 8 OBS PT 0
0 -0.000382 0.01695564 0 0 1658
N N35282.726 33023.024 175.67 TOWER @. 6B
SOUTH PED 0 0
0.00482913 0.03627593 0 0 2015 Y
Y31892.772 32960.261 153.253 OBS PT 3B SOUTH
100.09468751 12.76626379 0.00531249 0.11706955
845.995612034910
" intPackageCount="" booGood="1" txtShotID=""
txtStatus="Field" qvTime="0" />
</tblFinalData>
</data>
</f2dbarchive>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDoc
DECLARE @.tableName varchar(80)
DECLARE @.instID int
SET @.instID=2096
SET @.tableName='tblFinalData'
IF(SELECT lngRecordCount FROM OPENXML(@.docHandle,
'/f2dbarchive/tables/Row', 1)
WITH (txtTablename varchar(80), lngRecordCount int)
WHERE txtTablename=@.tableName) > 0
BEGIN
print 'at least 1 row in '+@.tableName
INSERT INTO tblFinalData
(lngInstanceID, binData, intPackageCount, booGood, txtShotID, txtStatus,
qvTime)
SELECT @.instID, cast(binData as varbinary) as binData, intPackageCount,
booGood, txtShotID, txtStatus, qvTime
FROM OpenXML(@.docHandle, '/f2dbarchive/data/tblFinalData/Row', 1)
WITH (
lngInstanceID int,
binData varbinary(2000),
intPackageCount int,
booGood bit,
txtShotID char(10),
txtStatus char(10),
qvTime decimal(16, 6)
)
END
ELSE
BEGIN
print '0 records in ' + @.tableName
END
EXEC sp_xml_removedocument @.docHandle
/**
--undesired unicode results: 1) it's unicode and 2) the data is blank after
the first number is translated
0x310030003900340036003500330039003600340030003000 300030003000
...
0x310030003900340036003500330034003200310030003000 300030003000
...
--normal result using vb
0x313039343635353933393030303030303030303032373920 20202020202046696E616C2020202020333531...
**/
The XML is Unicode once it is parsed. If you want to get it into
non-Unicode, change

> binData varbinary(2000),
to

> binData varchar(8000),
in your WITH clause.
HTH
Michael
"TeraComp in Florida" <TeraComp in Florida@.discussions.microsoft.com> wrote
in message news:3F1475DE-B508-4F80-B7DF-68C40E627FAD@.microsoft.com...
> /***
> Requirement: Insert ascii text into varbinary field allowing SQL to
> convert
> the data implicitly
> Problem: The data is encoded as Unicode and is truncated or simply
> blanked
> out after the first 10 characters
> The following is valid and works; however, it doesn't solve my problem.
> As a result, I've tried several approaches, including creating a cursor
> and
> calling Cast() to convert the data to binary. This appears to work, but
> again, stops after the first 10 characters
> **/
> DECLARE @.docHandle int
> DECLARE @.xmlDoc varchar(5000)
> SET @.xmlDoc ='
> <?xml version="1.0"?>
> <f2dbarchive>
> <archive_timestamp datetime="2004-Sep-27 12:28:48 AM" />
> <archive_test id="1530" />
> <!-- tables used in this archive -->
> <tables>
> <Row txtTablename="tblFinalData" txtKeyName="lngInstanceID"
> lngKeyValue="2096" lngRecordCount="4" />
> </tables>
> <!-- data from the tables -->
> <data>
> <tblFinalData>
> <Row lngDataID="5336" lngInstanceID="2096"
> binData="10946534210000000000276
> Final
> 35031.16534415632448.030617561162.062056640780.437 87345288720.529611595975413.8801526137533849.47590 9519025.77227602431221.58205664078257.545
> 0.085 DUST SPOTTER 3 LOF 85.00
> NY3849.475909519025.7722760243122.0620566407829311 98.67
> 32086.86
> 160 85 31198.67 32086.86
> 140.48 85 0 0 1656
> Y Y34149.809 33025.014 168.247 TOWER @. 5B SOUTH PED
> 123.21566117 -0.30555699 0.0010055 0.00555699
> 162.7038151636657.46 2017 Y Y34642.169 33069.594
> 172.887 MND 5.5B_NEW
> 147.95450669 -0.39920593 0.02882665 0.34920593
> 167.814226706570
> 2019 N N31198.912 32050.403 140.93
> GP
> 8 OBS PT 0 0
> -0.000382 0.01695564 0 0 1658
> Y
> Y35282.726 33023.024 175.67 TOWER @. 6B SOUTH PED
> 203.64517087 -1.08627593 0.00482913 0.03627593
> 163.7961106000857.63 2015 N N31892.772 32960.261
> 153.253 OBS PT 3B SOUTH 0
> 0 0.00531249 0.11706955 0 0
> "
> intPackageCount="" booGood="1" txtShotID="" txtStatus="Field" qvTime="0"
> />
> <Row lngDataID="5337" lngInstanceID="2096"
> binData="10946539640000000000277
> Final
> 34900.43868810932427.234930849677.0883333717911.85 11700763559.4096332145782681.194252534983717.38436 4552516.449301983071536.608333371790
> 0 1ST LIGHT SPOTTER 3
> NY3717.384364552516.449301983071517.08833337179311 98.67
> 32086.86 160 85 31198.67
> 32086.86 140.48 85 0 0 1656
> Y Y34149.809 33025.014 168.247 TOWER @. 5B
> SOUTH
> PED 128.4489945 34.57777634 0.0010055
> 0.00555699 829.755965865400 2017 Y Y34642.169
> 33069.594 172.887 MND 5.5B_NEW
> 158.30450669 44.65079407 0.02882665 0.34920593
> 856.898004060910 2019 N N31198.912 32050.403
> 140.93 GP 8 OBS PT 0
> 0 -0.000382 0.01695564 0 0
> 1658
> N N35282.726 33023.024 175.67 TOWER @. 6B
> SOUTH PED 0 0
> 0.00482913 0.03627593 0 0 2015
> Y
> Y31892.772 32960.261 153.253 OBS PT 3B SOUTH
> 100.09468751 12.76626379 0.00531249 0.11706955
> 845.995612034910
> " intPackageCount="" booGood="1" txtShotID=""
> txtStatus="Field" qvTime="0" />
> </tblFinalData>
> </data>
> </f2dbarchive>'
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDoc
> DECLARE @.tableName varchar(80)
> DECLARE @.instID int
> SET @.instID=2096
> SET @.tableName='tblFinalData'
> IF (SELECT lngRecordCount FROM OPENXML(@.docHandle,
> '/f2dbarchive/tables/Row', 1)
> WITH (txtTablename varchar(80), lngRecordCount int)
> WHERE txtTablename=@.tableName) > 0
> BEGIN
> print 'at least 1 row in '+@.tableName
> INSERT INTO tblFinalData
> (lngInstanceID, binData, intPackageCount, booGood, txtShotID, txtStatus,
> qvTime)
> SELECT @.instID, cast(binData as varbinary) as binData, intPackageCount,
> booGood, txtShotID, txtStatus, qvTime
> FROM OpenXML(@.docHandle, '/f2dbarchive/data/tblFinalData/Row', 1)
> WITH (
> lngInstanceID int,
> binData varbinary(2000),
> intPackageCount int,
> booGood bit,
> txtShotID char(10),
> txtStatus char(10),
> qvTime decimal(16, 6)
> )
> END
> ELSE
> BEGIN
> print '0 records in ' + @.tableName
> END
>
> EXEC sp_xml_removedocument @.docHandle
> /**
> --undesired unicode results: 1) it's unicode and 2) the data is blank
> after
> the first number is translated
> 0x310030003900340036003500330039003600340030003000 300030003000
> ...
> 0x310030003900340036003500330034003200310030003000 300030003000
> ...
> --normal result using vb
> 0x313039343635353933393030303030303030303032373920 20202020202046696E616C2020202020333531...
> **/
|||Thanks...that's great...gets me past creating a cursor. If I may be so dense
to ask one more issue:
After processing, this is all I get:
0x313039343635333432313030303030303030303032373620 202020202020
From a source string of:
10946534210000000000276 Final
35031.16534415632448.030617561162.062056640780.437 8734528 {the string is
actually about 1600 characters wide}
Is there something wrong with mixed numeric/text?
Any followup is greatly appreciated.
Dave
"Michael Rys [MSFT]" wrote:

> The XML is Unicode once it is parsed. If you want to get it into
> non-Unicode, change
>
> to
>
> in your WITH clause.
> HTH
> Michael
> "TeraComp in Florida" <TeraComp in Florida@.discussions.microsoft.com> wrote
> in message news:3F1475DE-B508-4F80-B7DF-68C40E627FAD@.microsoft.com...
>
>
|||I've discovered that cast(binData as binary(2000)) properly stores the data.
My guess is SQL Sever see the space as a field terminator and stops
processing.
If this is right, the follow-on question should be, how do I force SQL to
read the entire data word?
Or am I stuck with fixed width binary data? (incidently, the reason for
using a varbinary column was for efficiency)
Thanks again...I'm feeling close to resolving my delimma.
Dave
"TeraComp in Florida" wrote:
[vbcol=seagreen]
> Thanks...that's great...gets me past creating a cursor. If I may be so dense
> to ask one more issue:
> After processing, this is all I get:
> 0x313039343635333432313030303030303030303032373620 202020202020
> From a source string of:
> 10946534210000000000276 Final
> 35031.16534415632448.030617561162.062056640780.437 8734528 {the string is
> actually about 1600 characters wide}
> Is there something wrong with mixed numeric/text?
> Any followup is greatly appreciated.
> Dave
> "Michael Rys [MSFT]" wrote:
|||How do you check? If you use the query analyzer, did you set the column
width large enough?
The space should not be treated as a field terminator...
Best regards
Michael
"TeraComp in Florida" <TeraCompinFlorida@.discussions.microsoft.com> wrote in
message news:FD29C515-44BF-4A4A-A61B-0290738F0AB8@.microsoft.com...[vbcol=seagreen]
> I've discovered that cast(binData as binary(2000)) properly stores the
> data.
> My guess is SQL Sever see the space as a field terminator and stops
> processing.
> If this is right, the follow-on question should be, how do I force SQL to
> read the entire data word?
> Or am I stuck with fixed width binary data? (incidently, the reason for
> using a varbinary column was for efficiency)
> Thanks again...I'm feeling close to resolving my delimma.
> Dave
> "TeraComp in Florida" wrote:
|||Found the bug:
cast(binData as varbinary) as binData
Should be
cast(binData as varbinary(2000)) as binData
SQL Server was kind enough to all the former, but this allow it to determine
the length of the varBinary data.
Many thanks to Michael Rys for expert guidance.
Dave
"TeraComp in Florida" wrote:
[vbcol=seagreen]
> I've discovered that cast(binData as binary(2000)) properly stores the data.
> My guess is SQL Sever see the space as a field terminator and stops
> processing.
> If this is right, the follow-on question should be, how do I force SQL to
> read the entire data word?
> Or am I stuck with fixed width binary data? (incidently, the reason for
> using a varbinary column was for efficiency)
> Thanks again...I'm feeling close to resolving my delimma.
> Dave
> "TeraComp in Florida" wrote:
|||Yes I use QA using select len(bindata) as ldata, bindata from tblFinalData
That eventually led me to discover the need for cast(binData as
varbinary(2000))
which solves my problem.
It would be noteworthy to discover why SQL Server stopped parsing the text
Try this:
Sql#1: select cast('1094653421000000000027635031.16534415632448. 03061756'
as varbinary) as tdata
Sql#2: select cast('1094653421000000000027635031.16534415632448. 03061756' as
varbinary(100)) as tdata
The results I get are
#1 is truncated to 30 characters (doesn't matter what characters)
#2 is complete and variable as expected
Therefore: (with my Florida hurricane-ridden mind), SQL Server's cast
function defaults to 30-characters. [guess I missed that in the Books-Online]
Dave
cast('somedata' as
"Michael Rys [MSFT]" wrote:

> How do you check? If you use the query analyzer, did you set the column
> width large enough?
> The space should not be treated as a field terminator...
> Best regards
> Michael
> "TeraComp in Florida" <TeraCompinFlorida@.discussions.microsoft.com> wrote in
> message news:FD29C515-44BF-4A4A-A61B-0290738F0AB8@.microsoft.com...
>
>
|||Hmm. I should have seen this (mea culpa) as well.
In SQL Server 2005, you probably want to use varbinary(max) instead :-).
Best regards
Michael
"TeraComp in Florida" <TeraCompinFlorida@.discussions.microsoft.com> wrote in
message news:2233608D-86CD-4EF3-AFCE-E22C274A8216@.microsoft.com...[vbcol=seagreen]
> Found the bug:
> cast(binData as varbinary) as binData
> Should be
> cast(binData as varbinary(2000)) as binData
> SQL Server was kind enough to all the former, but this allow it to
> determine
> the length of the varBinary data.
> Many thanks to Michael Rys for expert guidance.
> Dave
>
> "TeraComp in Florida" wrote:

Implicit queries

Implicit queries seem to increase locks. I was wondering
why?
ThanksWhat exactly do you mean by "Implicit queries"?
--
Andrew J. Kelly
SQL Server MVP
"Jay Kay" <jeelanik@.yahoo.com> wrote in message
news:08ff01c38911$5050fc80$a101280a@.phx.gbl...
> Implicit queries seem to increase locks. I was wondering
> why?
> Thanks|||Andrew,
Explicit transacitons are started with "BEGIN TRANSACTION" and end when the
transaction is either committed or rolled back, therefore the lock is held
for only that period.
Implicit Transactions
When a connection is operating in implicit transaction mode, Microsoft=AE SQL
ServerT automatically starts a new transaction after the current transaction
is committed or rolled back. You do nothing to delineate the start of a
transaction; you only commit or roll back each transaction. Implicit
transaction mode generates a continuous chain of transactions.
Thanks.
>--Original Message--
>What exactly do you mean by "Implicit queries"?
>-- >Andrew J. Kelly
>SQL Server MVP
>
>"Jay Kay" <jeelanik@.yahoo.com> wrote in message
>news:08ff01c38911$5050fc80$a101280a@.phx.gbl...
>> Implicit queries seem to increase locks. I was wondering
>> why?
>> Thanks
>
>.
>|||I know what Implicit Transactions are but you said Queries and I wasn't sure
that is what you meant.
Usually when you use Explicit trans your transactions are short and you only
have code in between the Begin and Commit that absolutely needs to be there.
What I see a lot with Implicit trans are that there is a lot of extra
statements that get "caught up " in the transaction that really don't need
to be there. Any extra statements increases the time it takes and also the
possibility of more locks simply because there is more being executed inside
the actual tran. One other thing you can look at is to see what the
isolation level is set to when the Implicit tran begins. Some clients
default to Serializable and that will definitely cause more locks.
--
Andrew J. Kelly
SQL Server MVP
"Jay Kay" <jeelanik@.yahoo.com> wrote in message
news:1f46a01c389cf$22b5c800$a601280a@.phx.gbl...
Andrew,
Explicit transacitons are started with "BEGIN
TRANSACTION" and end when
the
transaction is either committed or rolled back, therefore
the lock is
held
for only that period.
Implicit Transactions
When a connection is operating in implicit transaction
mode, Microsoft®
SQL
ServerT automatically starts a new transaction after the
current
transaction
is committed or rolled back. You do nothing to delineate
the start of a
transaction; you only commit or roll back each
transaction. Implicit
transaction mode generates a continuous chain of
transactions.
Thanks.
>--Original Message--
>What exactly do you mean by "Implicit queries"?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Jay Kay" <jeelanik@.yahoo.com> wrote in message
>news:08ff01c38911$5050fc80$a101280a@.phx.gbl...
>> Implicit queries seem to increase locks. I was
wondering
>> why?
>> Thanks
>
>.
>

implicit KeepTogether on subreports

I just read from another post that there is an implicit KeepTogether on
Subreports. Apparently, this is a property that cannot be turned off
and causes subreport data to be pushed to a new page if it can't fit on
the current page.
The only advice given was to avoid using subreports if you don't want
this behavior.
Aren't there reports that cannot be written without using subreports?
Perhaps I'm just not being creative enough, but I'm pretty sure
subreports are neccesary for the type of report I'm running. Am I just
out of luck?I have trouble doing parent child things (using separate queries) without
subreports - but it is what it is...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Floor" <floorflux@.gmail.com> wrote in message
news:1125088603.349930.167190@.z14g2000cwz.googlegroups.com...
>I just read from another post that there is an implicit KeepTogether on
> Subreports. Apparently, this is a property that cannot be turned off
> and causes subreport data to be pushed to a new page if it can't fit on
> the current page.
> The only advice given was to avoid using subreports if you don't want
> this behavior.
> Aren't there reports that cannot be written without using subreports?
> Perhaps I'm just not being creative enough, but I'm pretty sure
> subreports are neccesary for the type of report I'm running. Am I just
> out of luck?
>

Implicit Keep Together For Export?

I have a report that has 3 nested lists. The report looks fine when I view it in the browser but when I print it or export it to another format it appears that keep together is on for each list because some pages are only partially filled. I do not have keep together turned on for any report control, and I don't have page break turned on for anything. This problem results in the report being many more pages than it needs to be. Can anyone help?Yes, list dataregions have an implicit KeepTogether. You might give tables a try instead. More control over KeepTogether is on the list for a future version.|||

Now nearly 5 months later is there _ANY_ hope to get this KeepTogether working properly? Searching the web I found some other people having that problem. Its working correct if you have few data and its working correct if you have much data, but if you have a certain amount in between this keeptogether bug appears.

|||now over a year later and a new version keeptogether is not avaliable on tables.. man this sucks!

Implicit Keep Together For Export?

I have a report that has 3 nested lists. The report looks fine when I view it in the browser but when I print it or export it to another format it appears that keep together is on for each list because some pages are only partially filled. I do not have keep together turned on for any report control, and I don't have page break turned on for anything. This problem results in the report being many more pages than it needs to be. Can anyone help?Yes, list dataregions have an implicit KeepTogether. You might give tables a try instead. More control over KeepTogether is on the list for a future version.|||

Now nearly 5 months later is there _ANY_ hope to get this KeepTogether working properly? Searching the web I found some other people having that problem. Its working correct if you have few data and its working correct if you have much data, but if you have a certain amount in between this keeptogether bug appears.

|||now over a year later and a new version keeptogether is not avaliable on tables.. man this sucks!

Implicit Keep Together For Export?

I have a report that has 3 nested lists. The report looks fine when I view it in the browser but when I print it or export it to another format it appears that keep together is on for each list because some pages are only partially filled. I do not have keep together turned on for any report control, and I don't have page break turned on for anything. This problem results in the report being many more pages than it needs to be. Can anyone help?Yes, list dataregions have an implicit KeepTogether. You might give tables a try instead. More control over KeepTogether is on the list for a future version.|||

Now nearly 5 months later is there _ANY_ hope to get this KeepTogether working properly? Searching the web I found some other people having that problem. Its working correct if you have few data and its working correct if you have much data, but if you have a certain amount in between this keeptogether bug appears.

|||now over a year later and a new version keeptogether is not avaliable on tables.. man this sucks!

Implicit Keep Together For Export?

I have a report that has 3 nested lists. The report looks fine when I view it in the browser but when I print it or export it to another format it appears that keep together is on for each list because some pages are only partially filled. I do not have keep together turned on for any report control, and I don't have page break turned on for anything. This problem results in the report being many more pages than it needs to be. Can anyone help?Yes, list dataregions have an implicit KeepTogether. You might give tables a try instead. More control over KeepTogether is on the list for a future version.|||

Now nearly 5 months later is there _ANY_ hope to get this KeepTogether working properly? Searching the web I found some other people having that problem. Its working correct if you have few data and its working correct if you have much data, but if you have a certain amount in between this keeptogether bug appears.

|||now over a year later and a new version keeptogether is not avaliable on tables.. man this sucks!

Implicit conversions using UDTs

I've written a CLR UDT that basically wraps a COM FILETIME. While using and testing this UDT, I've come across some unusual behavior that I can't seem to track down. My question is this:

When I make the T-SQL statements

DECLARE @.ft dbo.MyFileTime
SET @.ft = 0x0123456789ABCDEF

What's happening within the SET statement?

My first surprise was that it succeeded. Then I thought, "The hex value, I understand, is implicitly a BINARY or a VARBINARY. Underneath, with those attributes, MyFileTime is also implicitly a Binary or a Varbinary. Maybe it's simply performing a direct assignment?"

MyFileTime is prefaced by the attributes:

[SqlUserDefinedTypeAttribute(Format.Native, IsByteOrdered = true, IsFixedLength = true]
[Serializable]
[StructLayout(LayoutKind.Sequential)]

One thing that definitely seems to be the case is that SET is not implicitly calling any code that I wrote. Also, the apparent binary representation of the stored value is not what I would expect it to be. If I manipulate @.ft via my properties, everything seems to work properly, but my use case doesn't allow me to do that in its context.

So, again, exactly what happens within the SET statement?

(The weird behavior has been exhibited on multiple versions of the beta, including the September CTP. The output methods I'm using to verify values (ToString(), e.g.) have been tested and verified with the precise values in question external to SqlServer. I'm reasonably confident that I'm not dealing with a simple logic error there.)

-Greg
I'm not entirely sure what problem you are facing, but I'll give it a shot. Let me know if this isn't what you were looking for.
You were right the first time when you thought that the Set command just performs a direct assignment - none of your UDT code is called for a Format.Native UDT. If your UDT was a Format.UserDefined UDT, then the BinaryReader code would have to run to instantiate it from the bytes.
I think the solution in your case is to define a UDT Validation Method. This validation method will be called whenever a UDT is defined directly by bytes (rather than going through the Parse method or something else) and you can use it to ensure that the bytes form a valid UDT.|||Hi, Steven, thanks for the reply. I apologize if my initial post was a bit obtuse. Reading back, it looks like I neglected to call out the precise issue I was having.
MyFileTime is wrapping two ints, dwHigh and dwLow, in that order. Potentially key to our discussion is that these are signed ints, not unsigned ints. When I attempt to assign a MyFileTime directly
SET @.ft = 0x0123456789ABCDEF
and then retrieve that value, the value I retrieve is:
0x8123456709ABCDEF
IE, the sign bits are toggled. That was the unexpected thing.
Right now I'm accessing the site from a different location, so I unfortunately can't provide the precise T-SQL statements that I was using to get these values.
Short version, then:
SET @.ft = 0x0123456789ABCDEF
-- Something like: PRINT cast(@.ft as binary(8))
0x8123456709ABCDEF
SET @.ft.DwHigh = 0x01234567
SET @.ft.DwLow = 0x89ABCDEF
-- I don't remember if this is req'd for type safety:
-- SET @.ft.DwLow = -1985229329 -- aka, 0x89ABCDEF
-- Something like: PRINT cast(@.ft as binary(8)), (IE, same output statement as above)
0x0123456789ABCDEF
The DwHigh and DwLow properties don't do anything exciting, it's a straight assignment to the underlying data.
Is it possible that the sign bits for those underlying ints are being toggled in the course of the direct assignment?
|||

Hi Greg, your concern makes a lot more sense now!

I understand your confusion and I believe I have an answer for you, but I'm actually seeing the reverse behavior from you. Can you check again to make sure? If your results are the same, can you attach your UDT source code?

Here is what I am seeing that hopefully explains things:

declare @.ft filetime
SET @.ft = 0x0123456789ABCDEF
select cast(@.ft as binary(8)), @.ft.ToString()
--The result matches exactly what was put in
--0x0123456789ABCDEF -2128394905,162254319
SET @.ft.dwHigh = 0x01234567
SET @.ft.dwLow = 0x89ABCDEF
select @.ft.dwHigh, cast(@.ft.dwHigh as binary(4))
--This also matches exactly what was put in
--19088743 0x01234567
select @.ft.dwLow, cast(@.ft.dwLow as binary(4))
--This also matches exactly what was put in
---1985229329 0x89ABCDEF
select cast(@.ft as binary(8)), @.ft.ToString()
--This output seems to be wrong, however the correct numbers are returned...
--0x8123456709ABCDEF 19088743,-1985229329

So, the question becomes why is setting the UDT binary to 0x0123456789ABCDEF not the same as setting the respective fields to 0x01234567 and 0x89ABCDEF?

The answer is Normalization. When SQL Server stores the UDT as a binary, it does so in such a way so that it can sort by the binary value (this is the IsByteOrdered=true attribute) rather than having to instantiate the UDT. It can't compare them in the regular Int binary format because as you can see with this data, 0x01234567 is less than 0x89ABCDEF when compared by binary values, but yet 19088743 is much greater than -1985229329.

Let me know if this compares with what you are seeing.

|||Hi, Steven!
My implementation of ToString() is a bit different, so instead of @.ft.ToString(), I tested this as follows:
DECLARE @.ft MyFiletime
SET @.ft = 0x0123456789ABCDEF
select cast(@.ft as binary(8)), @.ft.DwHigh, @.ft.DwLow
-- The binary result matches exactly what was put in
-- 0x0123456789ABCDEF
-- The decimal results appear to have their sign bits toggled
-- -2128394905,162254319
SET @.ft.DwHigh = 0x01234567
SET @.ft.DwLow = 0x89ABCDEF
select @.ft.DwHigh, cast(@.ft.DwHigh as binary(4))
-- This matches exactly what was put in
-- 19088743 0x01234567
select @.ft.DwLow, cast(@.ft.DwLow as binary(4))
-- This also matches exactly what was put in
-- -1985229329 0x89ABCDEF
select cast(@.ft as binary(8)), @.ft.DwHigh, @.ft.DwLow
--This output seems to be wrong, however the correct numbers are returned...
--0x8123456709ABCDEF 19088743,-1985229329
My results are the same as yours, so the normalization due to the IsByteOrdered property does seem to be the culprit.
I'd like to note that the first SELECT's decimal values, in both your and my samples, do have the sign bits toggled again, presumably because the normalization takes place between the MyFiletime CLR code and Sql Server's storage of the value. It seems, then, that the surprise is occurring because we're sort-of violating the MyFiletime interface by accessing the underlying bits. Of course, I don't need to say that in our particular use-case this is something of a necessity.
See, I find this behavior of the IsByteOrdered property to be unexpected. When I read the documentation for it while writing this UDT (which was, admittedly, some 6-7 months ago), I had the impression that "IsByteOrdered" would be a relatively "dumb" property. I didn't expect, and (apparently) didn't want, Sql Server and the CLR to interpret the semantics of my underlying types. Rather, I expected it to simply look at the byte representation and sort on it as it was. IE, I interpreted "IsByteOrdered" to be a "trust me, I know what I'm doing" property. With the set of attributes I used on this UDT, I didn't expect the internal byte representation of a MyFiletime to be any different between a byte array, a pair of ints, a pair of uints, a single long, and a single ulong.
Regarding solutions, then, my first thought is that converting those ints into uints ought to provide the desired behavior. It seems that the normalization is taking place because of the semantic meaning of a signed integral value.
If I correct the internal semantics of MyFiletime and make dwHigh and dwLow uints, would you also expect that to result in my desired behavior?
|||Hi Greg,
The serialization format does not actually depend on the IsByteOrdered=true attribute, I was just referencing it for the reason why SQL serializes the UDT in this way. It will be the same serialization whether IsByteOrdered=true or false.
The real reason you're not getting the results you expect is because you specified a Format.Native UDT, which is saying to SQL Server that you want SQL Server to serialize the UDT for you so that you don't have to deal with it.
If your use case depends on you dealing with the whole UDT on a byte level, then you may want to consider implementing it as a Format.UserDefined UDT, where you control the serialization completely. In this case, IsByteOrdered is a "trust me" attribute as SQL Server cannot verify that your serialization is in-fact Byte-Ordered.
Using Unsigned Integers internally in this case will also result in the serialization you expected because Unsigned Ints are naturally byte-ordered, but in general you should use a Format.UserDefined UDT if you require a specific serialization.
Did this clear things up?|||That clears it up nicely, Steven. Thanks again for the info!
As an aside, it may be useful to call out this normalization process in the BOL. I'm sure I'm not the only person who'll make this mistake.
-Greg
|||My co-worker Isaac just posted an entry to the SQL CLR blog about this issue which hopefully should help other people with similar problems.
Check out http://blogs.msdn.com/sqlclr/ for the entry and feel free to comment there with any feedback or suggestions for future posts.

Implicit conversion of datatype text to nvarchar is not allowed.

I am facing a problem while using SQL Server with VB application.

Implicit conversion from datatype text to nvarchar is not allowed.
Use the convert function to run this query.

When i see the trace file, i see one stored procedure called but no
lines of code get executed, and immediately after that the ROLLBACK
TRANSACTION occurs and the applications fails.

But to my surprise i am able to do the same thing on a different
machine using the same application and the same database on the same
server with the same user id.

Can anyone explain the reason of occurance of this problem.

I require this very urgently, so i will be oblized if anyone can come
up with a quick response.

Kind Regards,
Amit KumarAmit (kumar_amit@.delhi.tcs.co.in) writes:
> I am facing a problem while using SQL Server with VB application.
> Implicit conversion from datatype text to nvarchar is not allowed.
> Use the convert function to run this query.
> When i see the trace file, i see one stored procedure called but no
> lines of code get executed, and immediately after that the ROLLBACK
> TRANSACTION occurs and the applications fails.
> But to my surprise i am able to do the same thing on a different
> machine using the same application and the same database on the same
> server with the same user id.
> Can anyone explain the reason of occurance of this problem.

It seems that the procedure has a parameter of the type nvarchar, but
the application tries to pass a text parameter.

Is that really the same executable you run on the two machines. Or could
it be that they are two different versions, and the bug has been fixed in
one of them?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9415F30B91F6DYazorman@.127.0.0.1>...
> Amit (kumar_amit@.delhi.tcs.co.in) writes:
> > I am facing a problem while using SQL Server with VB application.
> > Implicit conversion from datatype text to nvarchar is not allowed.
> > Use the convert function to run this query.
> > When i see the trace file, i see one stored procedure called but no
> > lines of code get executed, and immediately after that the ROLLBACK
> > TRANSACTION occurs and the applications fails.
> > But to my surprise i am able to do the same thing on a different
> > machine using the same application and the same database on the same
> > server with the same user id.
> > Can anyone explain the reason of occurance of this problem.
> It seems that the procedure has a parameter of the type nvarchar, but
> the application tries to pass a text parameter.
> Is that really the same executable you run on the two machines. Or could
> it be that they are two different versions, and the bug has been fixed in
> one of them?

No both the executables are exactly the same, and we are not passing
any text parameters via the application.

Also i found that there may be a problem due to some ODBC drivers not
able to adjust with the UNICODE datatypes. Because if the change the
datatype to non-Unicode then things work on fine. Now i want to know
what may the specific reason for the problems with non-unicode
datatypes...may be the windows NT or the ODBC drivers are the catch.
But i am not sure what and where to search for these.

Thanks,
Amit Kumar|||Amit (kumar_amit@.delhi.tcs.co.in) writes:
> Also i found that there may be a problem due to some ODBC drivers not
> able to adjust with the UNICODE datatypes. Because if the change the
> datatype to non-Unicode then things work on fine. Now i want to know
> what may the specific reason for the problems with non-unicode
> datatypes...may be the windows NT or the ODBC drivers are the catch.
> But i am not sure what and where to search for these.

Sounds like you should make sure that the machines have some good version
of the MDAC installed. Search around at www.microsoft.com. The MDAC
Component Checker can be a good thing to start with, although I've found
that it can get confused, if you have some newer version it does not
know about.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9417425FAA96Yazorman@.127.0.0.1>...
> Amit (kumar_amit@.delhi.tcs.co.in) writes:
> > Also i found that there may be a problem due to some ODBC drivers not
> > able to adjust with the UNICODE datatypes. Because if the change the
> > datatype to non-Unicode then things work on fine. Now i want to know
> > what may the specific reason for the problems with non-unicode
> > datatypes...may be the windows NT or the ODBC drivers are the catch.
> > But i am not sure what and where to search for these.
> Sounds like you should make sure that the machines have some good version
> of the MDAC installed. Search around at www.microsoft.com. The MDAC
> Component Checker can be a good thing to start with, although I've found
> that it can get confused, if you have some newer version it does not
> know about.

I have already tried re-installation of the MDAC 2.5 but it did not
help me.
Can you give me idea if the regional settinngs could have created the
problem.
If yes, what must be the settings that creates this problem.

Also i found out that the Active Code Pages (ACP) also has an affect
and could be a reason for this problem. Do you have any idea of Active
Code Pages.

Thanks & Regards,
Amit|||Amit (kumar_amit@.delhi.tcs.co.in) writes:
> I have already tried re-installation of the MDAC 2.5 but it did not
> help me.
> Can you give me idea if the regional settinngs could have created the
> problem.
> If yes, what must be the settings that creates this problem.
> Also i found out that the Active Code Pages (ACP) also has an affect
> and could be a reason for this problem. Do you have any idea of Active
> Code Pages.

I thought ACP was for ANSI Code Page, but I could be wrong.

I don't really see where the regional settings would come in here, but
I've been wrong before.

Anyway, I have to admit that I am bit stumped. Is it possible for you
to share the source code that is causing the problem, both on the client
side and SQL Server side?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp