Increase Indentity value

How do I increase the current Indentity value for an existing Indentity column?
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want it to return: 99999
dbcc checkident
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>
|||Ken,
DBCC CHECKIDENT (Quote, RESEED, 99999)
GO
Cheers,
Paul
|||http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>
|||Excellent, I have bookmarked this.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Aaron [SQL Server MVP] wrote:
> http://www.aspfaq.com/5003
>

Increase Indentity value

How do I increase the current Indentity value for an existing Indentity colu
mn?
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want it to return: 99999dbcc checkident
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>|||Ken,
DBCC CHECKIDENT (Quote, RESEED, 99999)
GO
Cheers,
Paul|||http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>|||Excellent, I have bookmarked this.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Aaron [SQL Server MVP] wrote:
> http://www.aspfaq.com/5003
>sql

Increase Identity value

How do I increase the current Identity value of an existing Identity column?
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want to increase it to, Returns: 99999
Thanks,
http://www.aspfaq.com/5007
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:C5A7066B-D4E3-48C3-B1E2-54B7E8DC4A14@.microsoft.com...
> How do I increase the current Identity value of an existing Identity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want to increase it to, Returns: 99999
> Thanks,

Increase Identity value

How do I increase the current Identity value of an existing Identity column?
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want to increase it to, Returns: 99999
Thanks,
http://www.aspfaq.com/2237
For more information, please see DBCC CHECKIDENT in Books Online.
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:4BE94FEA-4CCF-4EA1-AE9E-F5A5B8057EAC@.microsoft.com...
> How do I increase the current Identity value of an existing Identity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want to increase it to, Returns: 99999
> Thanks,
>

Increase Identity value

How do I increase the current Identity value of an existing Identity column?
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want to increase it to, Returns: 99999
Thanks,http://www.aspfaq.com/2237
For more information, please see DBCC CHECKIDENT in Books Online.
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:4BE94FEA-4CCF-4EA1-AE9E-F5A5B8057EAC@.microsoft.com...
> How do I increase the current Identity value of an existing Identity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want to increase it to, Returns: 99999
> Thanks,
>

Incorrect value returned from Stored Procedure

I have an asp.net 1.1 website that uses sql server 2000 and vb.

I have a bit of a dilema, when I run a stored procedure in a webpage it returns the wrong value, but if I run it

in the query analyzer the correct value is returned.

 Dim orderHistory As nlb.OrdersDB = New nlb.OrdersDB ' Obtain Order ID from QueryString Dim OrderID As Integer = CInt(Request.Params("ID")) ' Get the customer ID too Dim myNewCustomerId As Integer = 0 myNewCustomerId = orderHistory.GetOrderCustomer(OrderID) Public Function GetOrderCustomer(ByVal orderID As Integer) As Integer ' Create Instance of Connection and Command Object Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As SqlCommand = New SqlCommand("nlbsp_OrdersCustomerID", myConnection) ' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure ' Add Parameters to SPROC Dim parameterOrderID As New SqlParameter("@.order_id", SqlDbType.Int, 4) parameterOrderID.Value = orderID myCommand.Parameters.Add(parameterOrderID) Dim parameterOrderCustID As New SqlParameter("@.customer_id", SqlDbType.Int, 4) parameterOrderCustID.Value = ParameterDirection.Output myCommand.Parameters.Add(parameterOrderCustID) 'Open the connection and execute the Command myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close() ' Return the customer_id (obtained as out paramter of SPROC) If parameterOrderCustID.Value <> 0 Then Return CInt(parameterOrderCustID.Value) Else Return 0 End If End Functionthe stored procdure isCREATE PROCEDURE [dbo].[nlbsp_OrdersCustomerID]( @.order_id int, @.customer_id int OUTPUT)AS/* Return the customer_id from the Orders. */SELECT @.customer_id = customer_id FROM nlb_Orders WHERE order_id = @.order_idGO

I know a particular order_id returns a value of 1. But when I run it in the webpage it always comes back as 2.

Any ideas would be appreciated

Thanks

Pete

Seems you made a small mistake:

parameterOrderCustID.Value = ParameterDirection.Output

It should be:

Dim CustomerID As Integer
parameterOrderCustID.Value = CustomerID
parameterOrderCustID.Direction=ParameterDirection.Output

Incorrect query results with embedded comments

The following two identical queries return different
results. The first one with comments embedded in the query
returns all rows (ignoring the condition after the
comment). The second query with no comment works fine.
My SQL Server version is:
Microsoft SQL Server 2000 - 8.00.679 (Intel X86) Aug 26
2002 15:09:48 Copyright (c) 1988-2000 Microsoft
Corporation Enterprise Edition on Windows NT 5.2 (Build
3718: )
--
select CLEC_EU_DISCONNECT_INFORMATION.DNUM
CLEC_EU_DISCONNECT_INFORMATION_DNUM,
CLEC_TXN_PON.PON_VER
CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
left outer join CLEC_EU_DISCONNECT_INFORMATION
on
CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATION
.DISC_INFO_PON_VER
and
CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DISC
_INFO_LOCNUM
--left outer join CLEC_EU_TRANSFER_CALLS on
--
CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TRA
NSFER_CALLS.TC_LOCNUM
--and
CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_TR
ANSFER_CALLS.TC_PON_VER
--and
CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS.
TC_DNUM
where CLEC_TXN_PON.PON_VER='217-00'
select CLEC_EU_DISCONNECT_INFORMATION.DNUM
CLEC_EU_DISCONNECT_INFORMATION_DNUM,
CLEC_TXN_PON.PON_VER
CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
left outer join CLEC_EU_DISCONNECT_INFORMATION
on
CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATION
.DISC_INFO_PON_VER
and
CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DISC
_INFO_LOCNUM
where CLEC_TXN_PON.PON_VER='217-00'Shame this lost it's formatting - but I'm guessing all the
joins are on single lines.
I've come across this a couple of times where comments
mess up (or help) a query but never with v2000.
Look at syscomments to see what the query looks like.
Especially the line termination character on the last line.
It's worthwhile just retyping the query too.
Are you using query analyser?
>--Original Message--
>The following two identical queries return different
>results. The first one with comments embedded in the
query
>returns all rows (ignoring the condition after the
>comment). The second query with no comment works fine.
>My SQL Server version is:
>Microsoft SQL Server 2000 - 8.00.679 (Intel X86) Aug
26
>2002 15:09:48 Copyright (c) 1988-2000 Microsoft
>Corporation Enterprise Edition on Windows NT 5.2 (Build
>3718: )
>--
>
>select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>CLEC_TXN_PON.PON_VER
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>left outer join CLEC_EU_DISCONNECT_INFORMATION
>on
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
N
>..DISC_INFO_PON_VER
>and
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
C
>_INFO_LOCNUM
>--left outer join CLEC_EU_TRANSFER_CALLS on
>--
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
A
>NSFER_CALLS.TC_LOCNUM
>--and
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
R
>ANSFER_CALLS.TC_PON_VER
>--and
>CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
.
>TC_DNUM
>where CLEC_TXN_PON.PON_VER='217-00'
>
>select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>CLEC_TXN_PON.PON_VER
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>left outer join CLEC_EU_DISCONNECT_INFORMATION
>on
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
N
>..DISC_INFO_PON_VER
>and
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
C
>_INFO_LOCNUM
>where CLEC_TXN_PON.PON_VER='217-00'
>.
>|||Hi Swami,
Does the problem occur if you place the two queries in Query Analyzer? I
think the command is not correctly generated.
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
--
| Content-Class: urn:content-classes:message
| From: "Swami Muthuvelu" <swami@.mclsystems.com>
| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
| Subject: Incorrect query results with embedded comments
| Date: Wed, 2 Jul 2003 11:34:41 -0700
| Lines: 78
| Message-ID: <463701c340c8$99cc8460$a401280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcNAyJnM9s4j20agSQe1sjFP5SfVvw==| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:22629
| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| The query mentioned below was a programmatically generated
| query, with lines ending with "Carriage return" (char(13))
| character, and did not have line feed (char(10)). When
| such generated SQL was copied and pasted into SQL Query
| analyzer displayed perfectly fine, but did not recognize
| the break of the line.
|
|
| >--Original Message--
| >The following two identical queries return different
| >results. The first one with comments embedded in the
| query
| >returns all rows (ignoring the condition after the
| >comment). The second query with no comment works fine.
| >
| >My SQL Server version is:
| >
| >Microsoft SQL Server 2000 - 8.00.679 (Intel X86) Aug
| 26
| >2002 15:09:48 Copyright (c) 1988-2000 Microsoft
| >Corporation Enterprise Edition on Windows NT 5.2 (Build
| >3718: )
| >
| >--
| >
| >
| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >CLEC_TXN_PON.PON_VER
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
|
| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >on
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| N
| >..DISC_INFO_PON_VER
| >and
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| C
| >_INFO_LOCNUM
| >--left outer join CLEC_EU_TRANSFER_CALLS on
| >--
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
| A
| >NSFER_CALLS.TC_LOCNUM
| >--and
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
| R
| >ANSFER_CALLS.TC_PON_VER
| >--and
| >CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
| .
| >TC_DNUM
| >where CLEC_TXN_PON.PON_VER='217-00'
| >
| >
| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >CLEC_TXN_PON.PON_VER
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
|
| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >on
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| N
| >..DISC_INFO_PON_VER
| >and
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| C
| >_INFO_LOCNUM
| >where CLEC_TXN_PON.PON_VER='217-00'
| >
| >.
| >
||||Here are two queries. Each one generates a SQL query. Copy
and paste the results of the query to the query pane of
query analyzer and run it. Both looks identical. The first
one does not work, but the second one does.
--
select 'select * ' + char(13) + '--comment--' + char(13)
+ 'from sysobjects'
select 'select * ' + char(13) + '--comment--' + char(10)
+ 'from sysobjects'
--
You are not considering a carriage return as a line break
in the server engine, but you are considering as a line
break for the query analyzer.
Swami
>--Original Message--
>Hi Swami,
>Does the problem occur if you place the two queries in
Query Analyzer? I
>think the command is not correctly generated.
>
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Regards,
>Bill Cheng
>Microsoft Support Engineer
>--
>| Content-Class: urn:content-classes:message
>| From: "Swami Muthuvelu" <swami@.mclsystems.com>
>| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
>| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
>| Subject: Incorrect query results with embedded comments
>| Date: Wed, 2 Jul 2003 11:34:41 -0700
>| Lines: 78
>| Message-ID: <463701c340c8$99cc8460$a401280a@.phx.gbl>
>| MIME-Version: 1.0
>| Content-Type: text/plain;
>| charset="iso-8859-1"
>| Content-Transfer-Encoding: 7bit
>| X-Newsreader: Microsoft CDO for Windows 2000
>| Thread-Index: AcNAyJnM9s4j20agSQe1sjFP5SfVvw==>| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>| Newsgroups: microsoft.public.sqlserver.server
>| Path: cpmsftngxa09.phx.gbl
>| Xref: cpmsftngxa09.phx.gbl
microsoft.public.sqlserver.server:22629
>| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>| X-Tomcat-NG: microsoft.public.sqlserver.server
>|
>| The query mentioned below was a programmatically
generated
>| query, with lines ending with "Carriage return" (char
(13))
>| character, and did not have line feed (char(10)). When
>| such generated SQL was copied and pasted into SQL Query
>| analyzer displayed perfectly fine, but did not
recognize
>| the break of the line.
>|
>|
>| >--Original Message--
>| >The following two identical queries return different
>| >results. The first one with comments embedded in the
>| query
>| >returns all rows (ignoring the condition after the
>| >comment). The second query with no comment works fine.
>| >
>| >My SQL Server version is:
>| >
>| >Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
Aug
>| 26
>| >2002 15:09:48 Copyright (c) 1988-2000 Microsoft
>| >Corporation Enterprise Edition on Windows NT 5.2
(Build
>| >3718: )
>| >
>| >--
>| >
>| >
>| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>| >CLEC_TXN_PON.PON_VER
>|
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>|
>| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>| >left outer join CLEC_EU_DISCONNECT_INFORMATION
>| >on
>|
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
>| N
>| >..DISC_INFO_PON_VER
>| >and
>|
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
>| C
>| >_INFO_LOCNUM
>| >--left outer join CLEC_EU_TRANSFER_CALLS on
>| >--
>|
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
>| A
>| >NSFER_CALLS.TC_LOCNUM
>| >--and
>|
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
>| R
>| >ANSFER_CALLS.TC_PON_VER
>| >--and
>|
>CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
>| .
>| >TC_DNUM
>| >where CLEC_TXN_PON.PON_VER='217-00'
>| >
>| >
>| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>| >CLEC_TXN_PON.PON_VER
>|
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>|
>| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>| >left outer join CLEC_EU_DISCONNECT_INFORMATION
>| >on
>|
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
>| N
>| >..DISC_INFO_PON_VER
>| >and
>|
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
>| C
>| >_INFO_LOCNUM
>| >where CLEC_TXN_PON.PON_VER='217-00'
>| >
>| >.
>| >
>|
>.
>|||Hi Swami,
I think the comment to be generated inside a single query causes the
problem. Could you make it generated before or after the query?
In addition, none of the query works on my side in Query Analyzer.
select 'select * ' + char(13) + '--comment--' + char(13)
+ 'from sysobjects'
select 'select * ' + char(13) + '--comment--' + char(10)
+ 'from sysobjects'
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
--
| Content-Class: urn:content-classes:message
| From: "Swami Muthuvelu" <swami@.mclsystems.com>
| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
<463701c340c8$99cc8460$a401280a@.phx.gbl>
<8zvyhVVQDHA.412@.cpmsftngxa09.phx.gbl>
| Subject: RE: Incorrect query results with embedded comments
| Date: Thu, 3 Jul 2003 07:06:21 -0700
| Lines: 155
| Message-ID: <0a1e01c3416c$47c51e80$a501280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcNBbEfCiVpFmLFfSJ+NOObv2qtfuw==| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:22750
| NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Here are two queries. Each one generates a SQL query. Copy
| and paste the results of the query to the query pane of
| query analyzer and run it. Both looks identical. The first
| one does not work, but the second one does.
|
| --
| select 'select * ' + char(13) + '--comment--' + char(13)
| + 'from sysobjects'
|
| select 'select * ' + char(13) + '--comment--' + char(10)
| + 'from sysobjects'
| --
|
| You are not considering a carriage return as a line break
| in the server engine, but you are considering as a line
| break for the query analyzer.
|
| Swami
|
|
|
| >--Original Message--
| >Hi Swami,
| >
| >Does the problem occur if you place the two queries in
| Query Analyzer? I
| >think the command is not correctly generated.
| >
| >
| >This posting is provided "AS IS" with no warranties, and
| confers no rights.
| >
| >Regards,
| >
| >Bill Cheng
| >Microsoft Support Engineer
| >--
| >| Content-Class: urn:content-classes:message
| >| From: "Swami Muthuvelu" <swami@.mclsystems.com>
| >| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
| >| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
| >| Subject: Incorrect query results with embedded comments
| >| Date: Wed, 2 Jul 2003 11:34:41 -0700
| >| Lines: 78
| >| Message-ID: <463701c340c8$99cc8460$a401280a@.phx.gbl>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| Thread-Index: AcNAyJnM9s4j20agSQe1sjFP5SfVvw==| >| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| >| Newsgroups: microsoft.public.sqlserver.server
| >| Path: cpmsftngxa09.phx.gbl
| >| Xref: cpmsftngxa09.phx.gbl
| microsoft.public.sqlserver.server:22629
| >| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| >| X-Tomcat-NG: microsoft.public.sqlserver.server
| >|
| >| The query mentioned below was a programmatically
| generated
| >| query, with lines ending with "Carriage return" (char
| (13))
| >| character, and did not have line feed (char(10)). When
| >| such generated SQL was copied and pasted into SQL Query
| >| analyzer displayed perfectly fine, but did not
| recognize
| >| the break of the line.
| >|
| >|
| >| >--Original Message--
| >| >The following two identical queries return different
| >| >results. The first one with comments embedded in the
| >| query
| >| >returns all rows (ignoring the condition after the
| >| >comment). The second query with no comment works fine.
| >| >
| >| >My SQL Server version is:
| >| >
| >| >Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
| Aug
| >| 26
| >| >2002 15:09:48 Copyright (c) 1988-2000 Microsoft
| >| >Corporation Enterprise Edition on Windows NT 5.2
| (Build
| >| >3718: )
| >| >
| >| >--
| >| >
| >| >
| >| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >| >CLEC_TXN_PON.PON_VER
| >|
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
| >|
| >| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >| >on
| >|
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| >| N
| >| >..DISC_INFO_PON_VER
| >| >and
| >|
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| >| C
| >| >_INFO_LOCNUM
| >| >--left outer join CLEC_EU_TRANSFER_CALLS on
| >| >--
| >|
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
| >| A
| >| >NSFER_CALLS.TC_LOCNUM
| >| >--and
| >|
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
| >| R
| >| >ANSFER_CALLS.TC_PON_VER
| >| >--and
| >|
| >CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
| >| .
| >| >TC_DNUM
| >| >where CLEC_TXN_PON.PON_VER='217-00'
| >| >
| >| >
| >| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >| >CLEC_TXN_PON.PON_VER
| >|
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
| >|
| >| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >| >on
| >|
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| >| N
| >| >..DISC_INFO_PON_VER
| >| >and
| >|
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| >| C
| >| >_INFO_LOCNUM
| >| >where CLEC_TXN_PON.PON_VER='217-00'
| >| >
| >| >.
| >| >
| >|
| >
| >.
| >
|sql

Incorrect page count on reports that use drill-down or have hidden

I am getting an incorrect page count on reports that use drill-down or where
report items are hidden. The Globals!TotalPages returns 1 on a report that
clearly has more than one page. On these reports, the Globals!TotalPages
returns the correct page count for the print preview. I get the same results
whether this function is in the header or footer. My goal is to display the
current page and total pages on each page.
If I remove the expression from the Hidden property I get the correct page
count.
I am using SQL 2005 and Reporting Services 2005.Hello mrScott,
Based on my test, the TotalPages will return correct result in my side when
never I use the hidden report item or drill down report.
Would you please send the report to me so that I can try to reproduce it on
my side? You could create a simple report based on the Sample database in
SQL 2005.
To reach me, please remove the ONLINE in my email address.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Scott,
I have tested on my side.
I think that you misunderstood this problem.
The pagecount you see in the preview is page count for the HTML layout.
When you click the Print Layout, since it will re pagenate the report
according to the page setting, you will get another page count.
If you zoom in the print preview in the Print Layout, you could see the
Pagecount in the up-right is 9.
This is the correct behavior.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Scott,
I have tested on my side.
I think that you misunderstood this problem.
The pagecount you see in the preview is page count for the HTML layout.
When you click the Print Layout, since it will re pagenate the report
according to the page setting, you will get another page count.
If you zoom in the print preview in the Print Layout, you could see the
Pagecount in the up-right is 9.
This is the correct behavior.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
If I understand you correctly, Globals!TotalPages returns one value for the
HTML layout and can return a different value for the Print Layout? Even
though the InteractiveSize and the PageSize are the same?
Is it possible to display Globals!TotalPages only for the Print Layout? Is
there a property or global variable that indicates whether the report is in
HTML or Print Layout? I don't need to display the page count for the HTML
layout but would like to display it when the report is printed.
Thanks again for you assistance.
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Scott,
When you print the report, the Globals!TotalPages will return the value for
the print layout.
You could ignore it when you get the HTML layout.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Wei Lu,
You were right in your second response to me, I misunderstood the problem.
The sections "Controlling Report Pagination" and " Understanding Report
Layout and Rendering" in " SQL Server 2005 Books Online" drove it home.
Since I can't get the page count to match in both layouts, ignoring it in
the HTML layout is the solution. I set the InteractiveSize Height to zero so
that the page count will always be one when the report is in the HTML layout
and greater than one in print layout. I can then hide the textbox containing
the page count in the HTML layout and show it in the print layout. If you
have a better idea for ignoring the page count in the HTML layout please let
me know.
Thanks again.
"Wei Lu [MSFT]" wrote:
> Hello Scott,
> When you print the report, the Globals!TotalPages will return the value for
> the print layout.
> You could ignore it when you get the HTML layout.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Scott,
For now I think ignorint the page count in the HTML layout is the only
thing you could do.
You could send your feedback to the product team if you have any concern.
http://connect.microsoft.com/sqlserver
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Incorrect Login associated with dbo

If I run the following statement and do not get any returns, does that mean I
have an incorrect login associated with the dbo?
use MY_DATABASE
go
select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
and d.name = 'MY_DATABASE'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
Yes. The dbo user sid should normally match the owner_sid in sys.databases.
The query below will should the names, if valid.
SELECT
d.name AS database_name,
SUSER_SNAME(d.owner_sid) AS databases_owner,
SUSER_SNAME(dp.sid) AS dbo_login
FROM sys.databases d
CROSS JOIN sys.database_principals dp
WHERE
d.name = 'MY_DATABASE'
AND dp.name = 'dbo'
You can correct the mismatch with ALTER AUTHORIZATION. For example:
ALTER AUTHORIZATION ON DATABASE::MY_DATABASE TO [sa];
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f875a7486c91@.uwe...
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
> use MY_DATABASE
> go
> select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
> sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
> and d.name = 'MY_DATABASE'
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>

Incorrect Login associated with dbo

If I run the following statement and do not get any returns, does that mean
I
have an incorrect login associated with the dbo?
use MY_DATABASE
go
select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
and d.name = 'MY_DATABASE'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
Yes. The dbo user sid should normally match the owner_sid in sys.databases.
The query below will should the names, if valid.
SELECT
d.name AS database_name,
SUSER_SNAME(d.owner_sid) AS databases_owner,
SUSER_SNAME(dp.sid) AS dbo_login
FROM sys.databases d
CROSS JOIN sys.database_principals dp
WHERE
d.name = 'MY_DATABASE'
AND dp.name = 'dbo'
You can correct the mismatch with ALTER AUTHORIZATION. For example:
ALTER AUTHORIZATION ON DATABASE::MY_DATABASE TO [sa];
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f875a7486c91@.uwe...
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
> use MY_DATABASE
> go
> select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
> sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
> and d.name = 'MY_DATABASE'
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>

Incorrect Login associated with dbo

If I run the following statement and do not get any returns, does that mean I
have an incorrect login associated with the dbo?
use MY_DATABASE
go
select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
and d.name = 'MY_DATABASE'
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
Yes. The dbo user sid should normally match the owner_sid in sys.databases.
The query below will should the names, if valid.
SELECT
d.name AS database_name,
SUSER_SNAME(d.owner_sid) AS databases_owner,
SUSER_SNAME(dp.sid) AS dbo_login
FROM sys.databases d
CROSS JOIN sys.database_principals dp
WHERE
d.name = 'MY_DATABASE'
AND dp.name = 'dbo'
You can correct the mismatch with ALTER AUTHORIZATION. For example:
ALTER AUTHORIZATION ON DATABASE::MY_DATABASE TO [sa];
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6f875a7486c91@.uwe...
> If I run the following statement and do not get any returns, does that
> mean I
> have an incorrect login associated with the dbo?
> use MY_DATABASE
> go
> select d.name, dp.name, d.owner_sid, dp.sid from sys.databases d,
> sys.database_principals dp where d.owner_sid = dp.sid and dp.name = 'dbo'
> and d.name = 'MY_DATABASE'
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>

Inconsistent sp_spaceused

SQL 2000 Enterprise, SP4, 8.00.2175
sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
name rows reserved data index_size
unused
-- -- -- -- --
--
table 0 7248 KB 5032 KB 32 KB
2184 KB
How come my table has 0 records yet still occupies space?Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> SQL 2000 Enterprise, SP4, 8.00.2175
> sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> name rows reserved data index_size
> unused
> -- -- -- -- --
> --
> table 0 7248 KB 5032 KB 32 KB
> 2184 KB
> How come my table has 0 records yet still occupies space?|||Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken space.
"Tibor Karaszi" wrote:
> Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
> news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> > SQL 2000 Enterprise, SP4, 8.00.2175
> >
> > sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> >
> > name rows reserved data index_size
> > unused
> > -- -- -- -- --
> > --
> > table 0 7248 KB 5032 KB 32 KB
> > 2184 KB
> >
> > How come my table has 0 records yet still occupies space?
>
>|||Leon Shargorodsky,
if you dropped a variable length column, you can reclaim the space using
"dbcc cleantable".
If it is a heap (table without clustered index), create a clustered index
and if you do not want to keep it then drop it.
If it is not a heap, use "dbcc dbreindex" or "alter index ... rebuild" if
you are using 2005.
AMB
"Leon Shargorodsky" wrote:
> Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken space.
> "Tibor Karaszi" wrote:
> > Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
> > news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> > > SQL 2000 Enterprise, SP4, 8.00.2175
> > >
> > > sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> > >
> > > name rows reserved data index_size
> > > unused
> > > -- -- -- -- --
> > > --
> > > table 0 7248 KB 5032 KB 32 KB
> > > 2184 KB
> > >
> > > How come my table has 0 records yet still occupies space?
> >
> >
> >|||sp_spaceused is not guaranteed to provide actual, up-to-the-minute correct
values. DO NOT rely on it for such.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> SQL 2000 Enterprise, SP4, 8.00.2175
> sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> name rows reserved data index_size
> unused
> -- -- -- -- --
> --
> table 0 7248 KB 5032 KB 32 KB
> 2184 KB
> How come my table has 0 records yet still occupies space?

Inconsistent sp_spaceused

SQL 2000 Enterprise, SP4, 8.00.2175
sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
name rows reserved data index_size
unused
-- -- -- -- --
--
table 0 7248 KB 5032 KB 32 KB
2184 KB
How come my table has 0 records yet still occupies space?Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in me
ssage
news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> SQL 2000 Enterprise, SP4, 8.00.2175
> sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> name rows reserved data index_size
> unused
> -- -- -- -- --
--
> --
> table 0 7248 KB 5032 KB 32 KB
> 2184 KB
> How come my table has 0 records yet still occupies space?|||Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken spac
e.
"Tibor Karaszi" wrote:

> Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
> news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
>
>|||Leon Shargorodsky,
if you dropped a variable length column, you can reclaim the space using
"dbcc cleantable".
If it is a heap (table without clustered index), create a clustered index
and if you do not want to keep it then drop it.
If it is not a heap, use "dbcc dbreindex" or "alter index ... rebuild" if
you are using 2005.
AMB
"Leon Shargorodsky" wrote:
[vbcol=seagreen]
> Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken sp
ace.
> "Tibor Karaszi" wrote:
>|||sp_spaceused is not guaranteed to provide actual, up-to-the-minute correct
values. DO NOT rely on it for such.
TheSQLGuru
President
Indicium Resources, Inc.
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> SQL 2000 Enterprise, SP4, 8.00.2175
> sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> name rows reserved data index_size
> unused
> -- -- -- -- --
--
> --
> table 0 7248 KB 5032 KB 32 KB
> 2184 KB
> How come my table has 0 records yet still occupies space?

Inconsistent behaviour of SQL

Folks, We have the following sql statement which we run against two
databases of same structure
But return different returns. Any help is appreciated.
Select @.@.version
go
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
Calist Site
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE =
'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM =
016598
go
The above works fine
Dub1 Site
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE =
'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM =
004811
go
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
Go
The red statement gets...
Fatal Error: Syntax error converting the varchar value '.W
_____
.. ' to a column of data type int.
The Table structure is as under (same for both the databases)
CREATE TABLE [dbo].[PLAYER] (
[INDIVIDUAL_ID] [int] NOT NULL ,
[PLAY_JOIN_DATE] [datetime] NULL ,
[PLAY_MBRSHIP_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_FINANCIAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_LEVEL] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PLAY_BADGE_NUM] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[PLAY_OCCUPATION] [char] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_MARITAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_INCOME_LEVEL] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NUMBER] [int] NOT NULL ,
[PLAY_INPLAY_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_CREATE_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_USERID] [char] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] WITH NOCHECK ADD
CONSTRAINT [PK_PLAYER] PRIMARY KEY CLUSTERED
(
[INDIVIDUAL_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] ADD
CONSTRAINT [FK_PLAYER_ACCOUNT] FOREIGN KEY
(
[ACCT_NUMBER]
) REFERENCES [dbo].[ACCOUNT] (
[ACCT_NUMBER]
),
CONSTRAINT [FK_PLAYER_INDIVIDUAL] FOREIGN KEY
(
[INDIVIDUAL_ID]
) REFERENCES [dbo].[INDIVIDUAL] (
[INDIVIDUAL_ID]
)
GO
Your problem (which you described with so much detail) can be
summarized by the following repro script:
CREATE TABLE Test (A varchar(5) PRIMARY KEY)
INSERT INTO Test VALUES ('123')
SELECT * FROM Test WHERE A=1
-- runs fine
INSERT INTO Test VALUES ('XYZ')
SELECT * FROM Test WHERE A=1
-- Syntax error converting the varchar value 'XYZ' to a column of data
type int.
This is expected behaviour, because when comparing a varchar column to
an int constant, SQL Server tries to convert the varchar to an int,
according to the data type precedence rules.
For more informations, see:
http://msdn.microsoft.com/library/en...da-db_2js5.asp
The solution is, of course, to specify a varchar constant (this way SQL
Server would not need to do any conversions, so it can also use an
index, if one exists).
Razvan
|||THX for the summary Razvan.
My question was:
SQL server does the conversion in all the databases my databases with the
same structure on the same sql server except this particular one.
Wondering if anyone has come across the situation where the SQL implicitly
converts (as per
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp)
Is this a particular patch thing, etc.
Regards,
Subhash
"Razvan Socol" wrote:

> Your problem (which you described with so much detail) can be
> summarized by the following repro script:
> CREATE TABLE Test (A varchar(5) PRIMARY KEY)
> INSERT INTO Test VALUES ('123')
> SELECT * FROM Test WHERE A=1
> -- runs fine
> INSERT INTO Test VALUES ('XYZ')
> SELECT * FROM Test WHERE A=1
> -- Syntax error converting the varchar value 'XYZ' to a column of data
> type int.
>
> This is expected behaviour, because when comparing a varchar column to
> an int constant, SQL Server tries to convert the varchar to an int,
> according to the data type precedence rules.
> For more informations, see:
> http://msdn.microsoft.com/library/en...da-db_2js5.asp
> The solution is, of course, to specify a varchar constant (this way SQL
> Server would not need to do any conversions, so it can also use an
> index, if one exists).
> Razvan
>
|||> SQL server does the conversion in all the databases my databases with the
> same structure on the same sql server except this particular one.
Probably, this is the only database that has some non-numeric values in
that char(6) column.
Razvan

Inconsistent behaviour of SQL

Folks, We have the following sql statement which we run against two
databases of same structure
But return different returns. Any help is appreciated.
Select @.@.version
go
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
Calist Site
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE =
'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM =
016598
go
The above works fine
Dub1 Site
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE =
'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM =
004811
go
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
Go
The red statement gets...
Fatal Error: Syntax error converting the varchar value '.W
_____
. ' to a column of data type int.
The Table structure is as under (same for both the databases)
CREATE TABLE [dbo].[PLAYER] (
[INDIVIDUAL_ID] [int] NOT NULL ,
[PLAY_JOIN_DATE] [datetime] NULL ,
[PLAY_MBRSHIP_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_FINANCIAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_LEVEL] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PLAY_BADGE_NUM] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[PLAY_OCCUPATION] [char] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_MARITAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_INCOME_LEVEL] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NUMBER] [int] NOT NULL ,
[PLAY_INPLAY_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_CREATE_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_USERID] [char] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] WITH NOCHECK ADD
CONSTRAINT [PK_PLAYER] PRIMARY KEY CLUSTERED
(
[INDIVIDUAL_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] ADD
CONSTRAINT [FK_PLAYER_ACCOUNT] FOREIGN KEY
(
[ACCT_NUMBER]
) REFERENCES [dbo].[ACCOUNT] (
[ACCT_NUMBER]
),
CONSTRAINT [FK_PLAYER_INDIVIDUAL] FOREIGN KEY
(
[INDIVIDUAL_ID]
) REFERENCES [dbo].[INDIVIDUAL] (
[INDIVIDUAL_ID]
)
GOYour problem (which you described with so much detail) can be
summarized by the following repro script:
CREATE TABLE Test (A varchar(5) PRIMARY KEY)
INSERT INTO Test VALUES ('123')
SELECT * FROM Test WHERE A=1
-- runs fine
INSERT INTO Test VALUES ('XYZ')
SELECT * FROM Test WHERE A=1
-- Syntax error converting the varchar value 'XYZ' to a column of data
type int.
This is expected behaviour, because when comparing a varchar column to
an int constant, SQL Server tries to convert the varchar to an int,
according to the data type precedence rules.
For more informations, see:
http://msdn.microsoft.com/library/e..._da-db_2js5.asp
The solution is, of course, to specify a varchar constant (this way SQL
Server would not need to do any conversions, so it can also use an
index, if one exists).
Razvan|||THX for the summary Razvan.
My question was:
SQL server does the conversion in all the databases my databases with the
same structure on the same sql server except this particular one.
Wondering if anyone has come across the situation where the SQL implicitly
converts (as per
http://msdn.microsoft.com/library/d...br />
2f3o.asp)
Is this a particular patch thing, etc.
Regards,
Subhash
"Razvan Socol" wrote:

> Your problem (which you described with so much detail) can be
> summarized by the following repro script:
> CREATE TABLE Test (A varchar(5) PRIMARY KEY)
> INSERT INTO Test VALUES ('123')
> SELECT * FROM Test WHERE A=1
> -- runs fine
> INSERT INTO Test VALUES ('XYZ')
> SELECT * FROM Test WHERE A=1
> -- Syntax error converting the varchar value 'XYZ' to a column of data
> type int.
>
> This is expected behaviour, because when comparing a varchar column to
> an int constant, SQL Server tries to convert the varchar to an int,
> according to the data type precedence rules.
> For more informations, see:
> http://msdn.microsoft.com/library/e..._da-db_2js5.asp
> The solution is, of course, to specify a varchar constant (this way SQL
> Server would not need to do any conversions, so it can also use an
> index, if one exists).
> Razvan
>|||> SQL server does the conversion in all the databases my databases with the
> same structure on the same sql server except this particular one.
Probably, this is the only database that has some non-numeric values in
that char(6) column.
Razvan

Inconsistent behaviour of SQL

Folks, We have the following sql statement which we run against two
databases of same structure
But return different returns. Any help is appreciated.
Select @.@.version
go
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
--
Calist Site
--
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE = 'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM = 016598
go
The above works fine
Dub1 Site
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE = 'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM = 004811
go
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
Go
The red statement gets...
Fatal Error: Syntax error converting the varchar value '.W
_____
. ' to a column of data type int.
The Table structure is as under (same for both the databases)
CREATE TABLE [dbo].[PLAYER] (
[INDIVIDUAL_ID] [int] NOT NULL ,
[PLAY_JOIN_DATE] [datetime] NULL ,
[PLAY_MBRSHIP_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_FINANCIAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_LEVEL] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PLAY_BADGE_NUM] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[PLAY_OCCUPATION] [char] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_MARITAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_INCOME_LEVEL] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NUMBER] [int] NOT NULL ,
[PLAY_INPLAY_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_CREATE_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_USERID] [char] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] WITH NOCHECK ADD
CONSTRAINT [PK_PLAYER] PRIMARY KEY CLUSTERED
(
[INDIVIDUAL_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] ADD
CONSTRAINT [FK_PLAYER_ACCOUNT] FOREIGN KEY
(
[ACCT_NUMBER]
) REFERENCES [dbo].[ACCOUNT] (
[ACCT_NUMBER]
),
CONSTRAINT [FK_PLAYER_INDIVIDUAL] FOREIGN KEY
(
[INDIVIDUAL_ID]
) REFERENCES [dbo].[INDIVIDUAL] (
[INDIVIDUAL_ID]
)
GOYour problem (which you described with so much detail) can be
summarized by the following repro script:
CREATE TABLE Test (A varchar(5) PRIMARY KEY)
INSERT INTO Test VALUES ('123')
SELECT * FROM Test WHERE A=1
-- runs fine
INSERT INTO Test VALUES ('XYZ')
SELECT * FROM Test WHERE A=1
-- Syntax error converting the varchar value 'XYZ' to a column of data
type int.
This is expected behaviour, because when comparing a varchar column to
an int constant, SQL Server tries to convert the varchar to an int,
according to the data type precedence rules.
For more informations, see:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp
The solution is, of course, to specify a varchar constant (this way SQL
Server would not need to do any conversions, so it can also use an
index, if one exists).
Razvan|||THX for the summary Razvan.
My question was:
SQL server does the conversion in all the databases my databases with the
same structure on the same sql server except this particular one.
Wondering if anyone has come across the situation where the SQL implicitly
converts (as per
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp)
Is this a particular patch thing, etc.
Regards,
Subhash
"Razvan Socol" wrote:
> Your problem (which you described with so much detail) can be
> summarized by the following repro script:
> CREATE TABLE Test (A varchar(5) PRIMARY KEY)
> INSERT INTO Test VALUES ('123')
> SELECT * FROM Test WHERE A=1
> -- runs fine
> INSERT INTO Test VALUES ('XYZ')
> SELECT * FROM Test WHERE A=1
> -- Syntax error converting the varchar value 'XYZ' to a column of data
> type int.
>
> This is expected behaviour, because when comparing a varchar column to
> an int constant, SQL Server tries to convert the varchar to an int,
> according to the data type precedence rules.
> For more informations, see:
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp
> The solution is, of course, to specify a varchar constant (this way SQL
> Server would not need to do any conversions, so it can also use an
> index, if one exists).
> Razvan
>|||> SQL server does the conversion in all the databases my databases with the
> same structure on the same sql server except this particular one.
Probably, this is the only database that has some non-numeric values in
that char(6) column.
Razvan

Incompatible data types in SQL EXP 2005

I'm trying to read a data table with all text fields using C# in ado.net. However, when my query runs, it always returns this error:

"The data types text and varchar are incompatible in the equal to operator. "

The SQL statment is "SELECT field1, field2 FROM table WHERE field1 = 'value'"

If I leave out the WHERE clause, it runs fine. There are NO varchar fields in my entire table (It's a test table). I've tried using both parameter objects and Convert (See commented section) with no luck. A packet trace shows the SQL string is being delivered in tact and the SQL server is returning the error. I don't know if it's a C# client issue, or a SQL Server 2005 Exp issue. The table was created using MS SQL Server Management Studio Express I'm currently using the SqlClient object like this:

<script runat="server">
DataSet dsData = new DataSet();
DataTable dtData = new DataTable();

void Page_Load()
{
string strConn = ConfigurationSettings.AppSettings["authstr"];
string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";

//string strSQL = "SELECT password_fld, pharmacy_fld FROM Esker_Tbl WHERE CONVERT(text, username_fld ) = '@.prmuser'";
//SqlParameter spuser = new SqlParameter("@.prmuser", SqlDbType.Text);

//string struser = "bobhope";
//spuser.Value = struser;
try
{
SqlConnection Conn = new SqlConnection(strConn);
SqlCommand Cmd = new SqlCommand(strSQL, Conn);
SqlDataAdapter Adp = new SqlDataAdapter(Cmd);

Conn.Open();
Adp.Fill(dsData, "USERS1");
dtData = dsData.Tables["USERS1"];
dgData.DataSource = dtData;
dgData.DataBind();
dgData.Visible = true;

}
catch (Exception ex)
{
string strmessage = "";
strmessage = "Unable to access database: " + ex.Message;
lblMessage.Text = strmessage;
}
return;
}

Have you pulled out the query and tried to run it in Management Studio to break out the conversion type differences? I don't have your data structure so I'm not sure what the issue is.

If you're trying to compare two text data types, you should use the CONTAINS or other search parameters. In fact, the TEXT data type is being phased out in favor of nvarchar(max), which can hold gigs of data per row. Check out this article for more:

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

|||

Hi Mark:

Not sure if this is an error caused by copy-paste the code into the forum, but if the code sample you provided here is correct, then it seems like you should've seen a compile error here:

string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";

There is an extra " .

if that's an copy-paste typo, could you please provide what is the data type declared for User_ID in User_ID table?

thanks

Incompatible data types in SQL EXP 2005

I'm trying to read a data table with all text fields using C# in ado.net. However, when my query runs, it always returns this error:

"The data types text and varchar are incompatible in the equal to operator. "

The SQL statment is "SELECT field1, field2 FROM table WHERE field1 = 'value'"

If I leave out the WHERE clause, it runs fine. There are NO varchar fields in my entire table (It's a test table). I've tried using both parameter objects and Convert (See commented section) with no luck. A packet trace shows the SQL string is being delivered in tact and the SQL server is returning the error. I don't know if it's a C# client issue, or a SQL Server 2005 Exp issue. The table was created using MS SQL Server Management Studio Express I'm currently using the SqlClient object like this:

<script runat="server">
DataSet dsData = new DataSet();
DataTable dtData = new DataTable();

void Page_Load()
{
string strConn = ConfigurationSettings.AppSettings["authstr"];
string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";

//string strSQL = "SELECT password_fld, pharmacy_fld FROM Esker_Tbl WHERE CONVERT(text, username_fld ) = '@.prmuser'";
//SqlParameter spuser = new SqlParameter("@.prmuser", SqlDbType.Text);

//string struser = "bobhope";
//spuser.Value = struser;
try
{
SqlConnection Conn = new SqlConnection(strConn);
SqlCommand Cmd = new SqlCommand(strSQL, Conn);
SqlDataAdapter Adp = new SqlDataAdapter(Cmd);

Conn.Open();
Adp.Fill(dsData, "USERS1");
dtData = dsData.Tables["USERS1"];
dgData.DataSource = dtData;
dgData.DataBind();
dgData.Visible = true;

}
catch (Exception ex)
{
string strmessage = "";
strmessage = "Unable to access database: " + ex.Message;
lblMessage.Text = strmessage;
}
return;
}

Have you pulled out the query and tried to run it in Management Studio to break out the conversion type differences? I don't have your data structure so I'm not sure what the issue is.

If you're trying to compare two text data types, you should use the CONTAINS or other search parameters. In fact, the TEXT data type is being phased out in favor of nvarchar(max), which can hold gigs of data per row. Check out this article for more:

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

|||

Hi Mark:

Not sure if this is an error caused by copy-paste the code into the forum, but if the code sample you provided here is correct, then it seems like you should've seen a compile error here:

string strSQL = "SELECT User_ID, User_Name FROM USERS1 WHERE User_ID = "'bobhope'";

There is an extra " .

if that's an copy-paste typo, could you please provide what is the data type declared for User_ID in User_ID table?

thanks