Bug, Can not create varchar(max) columns with SMO

I just want to create a varchar(max) column with SMO. My code is as follows. Instead of varchar(max), a column with datatype varchar(1) is created!!!

Table t = new Table(db, "t1");
t.Columns.Add(new Column(t, "c1", new DataType(SqlDataType.NVarCharMax)));
t.Alter();

That seems to be a bug of release version. I don't face this problem in Beta 2

Is there a workaround for that problemThis seems like bug to me also. I will file a bug against the product. As a workaround, you can execute the T-SQL directly, such as

db.ExecuteNonQuery("create table tb (id nvarchar(max))");

Peter

|||Thanks for your help.|||Just ran across the same issue. Any other workaround on this?|||Hi

I just encountered the same issue with Sql Server 2005 SP1. However, it has only happened on one computer. The problem is not reproducible on any other machines in our environment.|||YOu should doublecheck that again, as in SP2 the following script is returned from the Scripter:

Code Snippet

USE [Northwind]

ALTER TABLE [dbo].[t1] ADD [c1] [nvarchar](max)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||The SMO library is invoked on a server that does not have Sql Server. The only Sql Server related libraries installed on the server are as follows.

XMO 9.00.1399.06 NOV 2005 Feature Pack
NATIVE CLIENT 9.00.1399.06 NOV 2005 Feature Pack
XML6.0 6.00.3890.0 NOV 2005 Feature Pack

Are there known issues with using these versions?

Thanks|||The SMO libraries are also updated within the service packs, so it could be that the actual stack which is generating the ALTER statement was bug fixed. I posted a bug before RTM time, which was also fixed with SP1, so there can be a good chance that the version you are using 1399 = RTM has the bug which is causing the problems.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Actually, you can set it - it's just a bit "hidden". Set the DataType to NVarChar and set an arbitrary length value (like 100). Then set the column's DataType.MaximumLength property to -1. It is now a NVarChar(max) column.

|||Thanks

I resolved the problem by upgrading the Sql Server Management Objects from the Nov 2005 release to the Feb 2007 release.

Bug, Can not create varchar(max) columns with SMO

I just want to create a varchar(max) column with SMO. My code is as follows. Instead of varchar(max), a column with datatype varchar(1) is created!!!

Table t = new Table(db, "t1");
t.Columns.Add(new Column(t, "c1", new DataType(SqlDataType.NVarCharMax)));
t.Alter();

That seems to be a bug of release version. I don't face this problem in Beta 2

Is there a workaround for that problem
This seems like bug to me also. I will file a bug against the product. As a workaround, you can execute the T-SQL directly, such as

db.ExecuteNonQuery("create table tb (id nvarchar(max))");

Peter

|||Thanks for your help.|||Just ran across the same issue. Any other workaround on this?|||Hi

I just encountered the same issue with Sql Server 2005 SP1. However, it has only happened on one computer. The problem is not reproducible on any other machines in our environment.

|||YOu should doublecheck that again, as in SP2 the following script is returned from the Scripter:

Code Snippet

USE [Northwind]

ALTER TABLE [dbo].[t1] ADD [c1] [nvarchar](max)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||The SMO library is invoked on a server that does not have Sql Server. The only Sql Server related libraries installed on the server are as follows.

XMO 9.00.1399.06 NOV 2005 Feature Pack
NATIVE CLIENT 9.00.1399.06 NOV 2005 Feature Pack
XML6.0 6.00.3890.0 NOV 2005 Feature Pack

Are there known issues with using these versions?

Thanks
|||The SMO libraries are also updated within the service packs, so it could be that the actual stack which is generating the ALTER statement was bug fixed. I posted a bug before RTM time, which was also fixed with SP1, so there can be a good chance that the version you are using 1399 = RTM has the bug which is causing the problems.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Actually, you can set it - it's just a bit "hidden". Set the DataType to NVarChar and set an arbitrary length value (like 100). Then set the column's DataType.MaximumLength property to -1. It is now a NVarChar(max) column.

|||Thanks

I resolved the problem by upgrading the Sql Server Management Objects from the Nov 2005 release to the Feb 2007 release.

Bug, Can not create varchar(max) columns with SMO

I just want to create a varchar(max) column with SMO. My code is as follows. Instead of varchar(max), a column with datatype varchar(1) is created!!!

Table t = new Table(db, "t1");
t.Columns.Add(new Column(t, "c1", new DataType(SqlDataType.NVarCharMax)));
t.Alter();

That seems to be a bug of release version. I don't face this problem in Beta 2

Is there a workaround for that problem
This seems like bug to me also. I will file a bug against the product. As a workaround, you can execute the T-SQL directly, such as

db.ExecuteNonQuery("create table tb (id nvarchar(max))");

Peter

|||Thanks for your help.|||Just ran across the same issue. Any other workaround on this?|||Hi

I just encountered the same issue with Sql Server 2005 SP1. However, it has only happened on one computer. The problem is not reproducible on any other machines in our environment.

|||YOu should doublecheck that again, as in SP2 the following script is returned from the Scripter:

Code Snippet

USE [Northwind]

ALTER TABLE [dbo].[t1] ADD [c1] [nvarchar](max)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||The SMO library is invoked on a server that does not have Sql Server. The only Sql Server related libraries installed on the server are as follows.

XMO 9.00.1399.06 NOV 2005 Feature Pack
NATIVE CLIENT 9.00.1399.06 NOV 2005 Feature Pack
XML6.0 6.00.3890.0 NOV 2005 Feature Pack

Are there known issues with using these versions?

Thanks
|||The SMO libraries are also updated within the service packs, so it could be that the actual stack which is generating the ALTER statement was bug fixed. I posted a bug before RTM time, which was also fixed with SP1, so there can be a good chance that the version you are using 1399 = RTM has the bug which is causing the problems.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Actually, you can set it - it's just a bit "hidden". Set the DataType to NVarChar and set an arbitrary length value (like 100). Then set the column's DataType.MaximumLength property to -1. It is now a NVarChar(max) column.

|||Thanks

I resolved the problem by upgrading the Sql Server Management Objects from the Nov 2005 release to the Feb 2007 release.

bug with TABLE variable??

Consider the following code but don't think about what it's supposed to do
as I've simplified it a lot.
The error I get is a syntax error when I try to save my proc so what this
proc is doing is not important.
DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
INSERT @.grpIds SELECT grp_id FROM tab_grp
DELETE
@.grpIds
FROM
@.grpIds
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = @.grpIds.grp_id
Error:
@.grpIds must be declared
If I replace the table variable by a normal table, there's no error any
more.
It seems to be a bug.
Should I use a temporary table then?
Thanks
HenriYou have to alias the table variable in the from clause, then you can
reference it in other parts of your statement:
DELETE
g
FROM
@.grpIds AS g
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = g.grp_id
--
Jacco Schalkwijk
SQL Server MVP
"Henri" <hmfireball@.hotmail.com> wrote in message
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>|||> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
Please see http://www.aspfaq.com/2475
Of particular interest:
"Table variables must be referenced by an alias, except in the FROM clause.
Consider the following two scripts: " [...]
--
http://www.aspfaq.com/
(Reverse address to reply.)
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>|||It works now :-)
Thanks a lot for your help,
and thanks for the interesting resource link too :-)
Henri
"Henri" <hmfireball@.hotmail.com> a écrit dans le message de
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>

bug with TABLE variable??

Consider the following code but don't think about what it's supposed to do
as I've simplified it a lot.
The error I get is a syntax error when I try to save my proc so what this
proc is doing is not important.
DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
INSERT @.grpIds SELECT grp_id FROM tab_grp
DELETE
@.grpIds
FROM
@.grpIds
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = @.grpIds.grp_id
Error:
@.grpIds must be declared
If I replace the table variable by a normal table, there's no error any
more.
It seems to be a bug.
Should I use a temporary table then?
Thanks
Henri
You have to alias the table variable in the from clause, then you can
reference it in other parts of your statement:
DELETE
g
FROM
@.grpIds AS g
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = g.grp_id
Jacco Schalkwijk
SQL Server MVP
"Henri" <hmfireball@.hotmail.com> wrote in message
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>
|||> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
Please see http://www.aspfaq.com/2475
Of particular interest:
"Table variables must be referenced by an alias, except in the FROM clause.
Consider the following two scripts: " [...]
http://www.aspfaq.com/
(Reverse address to reply.)

> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>
|||It works now :-)
Thanks a lot for your help,
and thanks for the interesting resource link too :-)
Henri
"Henri" <hmfireball@.hotmail.com> a crit dans le message de
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>

Bug with InScope() and some custom code

Hi guys,

i was developing some custom code to do a running total in a matrix, and i have noticed some odd behaviour with the InScope function. I am doing year on year reporting, so i have two row groups on my matrix: the first is on month (matrix2_Calendar_Month), the second on year (matrix2_Calendar_Year).

I needed to total the number of days covered by the months i was reporting on, so i wrote some very standard code to do this, along with an expression in that column of the matrix:

=IIf(
InScope("matrix2_Calendar_Year"),
CStr( Round( Sum(Fields!Sales.Value / (24 * Code.AddDays( CStr(Fields!Calendar_Month.Value), CInt(Fields!Calendar_Year.Value))), 2)) ,
Code.getBounds()
)

Code.AddDays() calculates and returns the number of days in the month of that year on that row. Code.getBounds simply returns the lower and upper bounds of the array, plus its contents (so i can inspect them). This is what is returned in the report:

	Month / Year		Sales	Capacity	% Capacity	Avg $/h
	February	2006	3842	7706	49.86%	2.86
		2007	0	0	0.00%	0
	March	2006	4949	8692	56.94%	3.33
		2007	0	0	0.00%	0
	April	2006	5160	8154	63.28%	3.58
		2007	0	0	0.00%	0
	May	2006	3309	8348	39.64%	2.22
		2007	0	0	0.00%	0
	Total		17259	32900	52.46%	0-8*28,28,31,31,30,30,31,31,28

If you look at the output in the total row, you will see that Code.AddDays() has been called one extra time at the end, with Feb 2006 as its parameters, thus adding an extra 28 days to the running total. Why is Code.AddDays called on the total row, when i should be out of the scope of both the row groups? (Note: this happens for whichever row group i use in the InScope check in the expression).

Here is the custom code used for all this:

Dim numDays()

Public Function AddDays(ByVal month As String, ByVal year As Integer) As Integer
Dim thisMonth As String

Dim upper As Integer
upper = 0
On Error Resume Next
upper = UBound(numDays) + 1
ReDim Preserve numDays(upper)

thisMonth = CStr(year) & "-" & month & "-01"
numDays(upper) = DateDiff("d", CDate(thisMonth), DateAdd("m", 1, CDate(thisMonth)))
AddDays = numDays(upper)
End Function

Public Function TotalDays() As Integer
Dim lower As Integer
Dim upper As Integer

lower = 0
upper = 0
On Error Resume Next
lower = LBound(numDays)
upper = UBound(numDays)

TotalDays = 0
Dim ii As Integer
For ii = lower To upper
TotalDays = TotalDays + CInt(numDays(ii))
Next
End Function

public function getBounds() as string
getBounds = Cstr(LBound(numDays)) & "-" & CStr(UBound(numDays)) & "*" & Join(numDays, ",")
end function

sluggy

This has nothing to do with InScope().

The AddDays function is used within a IIF function call. IIF (like any other VB function call) evaluates all arguments before the function is invoked. Hence, the AddDays function is invoked in all cases.

Suggestion:
1. change the expression in the matrix cell to:
=Code.MyCalculation(InScope("matrix2_Calendar_Year"))

2. add a custom code function MyCalculation which uses IF - ELSE blocks to call the other custom code functions. Only in the case of using the conditional IF statement (instead of the IIF function) you will achieve the desired effect.

-- Robert

|||

Doh, thanks Robert, i should have known that I last did VB a few years ago, i've obviously forgotten a bit :)

sluggy

|||

No problem. I'm glad I could help resolving your issue and there is no bug in InScope :)

-- Robert

Bug is com.microsoft.sqlserver.jdbc.SQLServerDataSource (2005 Beta

The following JUnit code fails:
// elided ...
public void testSelectMethod()
{
SQLServerDataSource source = new SQLServerDataSource();
source.setSelectMethod("cursor");
assertEquals("cursor", source.getSelectMethod());
}
// elided ...
with:
junit.framework.ComparisonFailure: Select method not set to cursor
expected:<cursor> but was:<null>
at junit.framework.Assert.assertEquals(Assert.java:81 )
at test.foo.assumptions.JTASQLTest.testSelectMethod(J TASQLTest.java:49)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154 )
at junit.framework.TestCase.runBare(TestCase.java:127 )
at junit.framework.TestResult$1.protect(TestResult.ja va:106)
at junit.framework.TestResult.runProtected(TestResult .java:124)
at junit.framework.TestResult.run(TestResult.java:109 )
at junit.framework.TestCase.run(TestCase.java:118)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:478)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:344)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:196)
jef - integralpath.blogs.com
Yes we changed the name of the SelectMethod property to
forwardReadOnlyMethod.
So you would say:
forwardReadOnlyMethod=direct
or
forwardReadOnlyMethod=serverCursor
Unfortunately we have not yet updated the SQLServerDataSource class to work
with this new setting yet.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Joe Weinstein" <joeNOSPAM@.bea.com> wrote in message
news:evctk3ozFHA.3312@.TK2MSFTNGP09.phx.gbl...
>
> jef wrote:
>
>
> Check the docs. I believe SelectMethod=cursor is now functionally
> meaningless with this version of the driver, and maybe they allow
> you to make the set call, just so it doesn't break existing code
> that was written for the previous driver, that shares zero code
> with this new one. They might also document that getSelectMethod()
> will do what it does...
> I think the best course is to verify my first contention, and
> then just not call either method for this one...
> Joe Weinstein at BEA
>
|||Note we are planning on changing this back to allowing selectMethod due to
huge customer demand.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"jef" <jef@.discussions.microsoft.com> wrote in message
news:5FB92280-7932-4722-84AC-72A3102980E5@.microsoft.com...
> The following JUnit code fails:
> // elided ...
> public void testSelectMethod()
> {
> SQLServerDataSource source = new SQLServerDataSource();
> source.setSelectMethod("cursor");
> assertEquals("cursor", source.getSelectMethod());
> }
> // elided ...
> with:
> junit.framework.ComparisonFailure: Select method not set to cursor
> expected:<cursor> but was:<null>
> at junit.framework.Assert.assertEquals(Assert.java:81 )
> at test.foo.assumptions.JTASQLTest.testSelectMethod(J TASQLTest.java:49)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:585)
> at junit.framework.TestCase.runTest(TestCase.java:154 )
> at junit.framework.TestCase.runBare(TestCase.java:127 )
> at junit.framework.TestResult$1.protect(TestResult.ja va:106)
> at junit.framework.TestResult.runProtected(TestResult .java:124)
> at junit.framework.TestResult.run(TestResult.java:109 )
> at junit.framework.TestCase.run(TestCase.java:118)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:478)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:344)
> at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:196)
>
> --
> jef - integralpath.blogs.com

Bug in 2005?

This code snippet works fine in 2000 but not in 2005. Is this a bug? I
get a conversion error on the 2005 server. Any ideas?
Create table Sample
(DESCR Char(30),
Ref Char(50),
Name Char(30))
Insert into sample
values ('Valid Row','03/25/2006 02:30:15 PM','Joe')
Insert into sample
values ('Something else','Not date related','Jack')
DECLARE @.Ref_DT datetime
SELECT @.Ref_DT = '03/25/2006 02:30:15 PM'
Select Name From Sample
Where SubString(DESCR, 1, 20) = 'Valid Row'
and Convert(datetime, REF, 121) = @.Ref_DTshub wrote:
> This code snippet works fine in 2000 but not in 2005. Is this a bug? I
> get a conversion error on the 2005 server. Any ideas?
>
Evaluation order is not guaranteed in any query so this can't be called
a bug even though it is inconvenient.
In the example given it would anyway be much better to make @.Ref_DT a
CHAR(50) instead of DATETIME. That way you can avoid the conversion for
each row.
If you must use CONVERT then try making a CASE expression of it (watch
out for line wrapping in this example):
...
WHERE SUBSTRING(DESCR, 1, 20) = 'Valid Row'
AND CASE WHEN REF
LIKE '[012][0-9]/[0123][0-9]/[12][0-9][0-9][0-9]
[012][0-9]:[0-5][0-9]:[0-5][0-9] [AP]M'
THEN CONVERT(DATETIME, REF, 121)
END = @.Ref_DT;
The above isn't foolproof but it does at least force the right
evaluation order (usually). In your case you can also use the ISDATE
function in place of my LIKE expression. ISDATE has the disadvantage
that it depends on implicit conversion so it isn't suitable for all
date formats.
The most important lesson is, don't store dates as strings.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||shub wrote:
> This code snippet works fine in 2000 but not in 2005. Is this a bug? I
> get a conversion error on the 2005 server. Any ideas?
> Create table Sample
> (DESCR Char(30),
> Ref Char(50),
> Name Char(30))
> Insert into sample
> values ('Valid Row','03/25/2006 02:30:15 PM','Joe')
> Insert into sample
> values ('Something else','Not date related','Jack')
> DECLARE @.Ref_DT datetime
> SELECT @.Ref_DT = '03/25/2006 02:30:15 PM'
> Select Name From Sample
> Where SubString(DESCR, 1, 20) = 'Valid Row'
> and Convert(datetime, REF, 121) = @.Ref_DT
>
The "bug" is that you're trying to convert a non-date value to a
DATETIME. How is this SQL's fault?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Bug in 2005?

This code snippet works fine in 2000 but not in 2005. Is this a bug? I
get a conversion error on the 2005 server. Any ideas?
Create table Sample
(DESCR Char(30),
Ref Char(50),
Name Char(30))
Insert into sample
values ('Valid Row','03/25/2006 02:30:15 PM','Joe')
Insert into sample
values ('Something else','Not date related','Jack')
DECLARE @.Ref_DT datetime
SELECT @.Ref_DT = '03/25/2006 02:30:15 PM'
Select Name From Sample
Where SubString(DESCR, 1, 20) = 'Valid Row'
and Convert(datetime, REF, 121) = @.Ref_DTshub wrote:
> This code snippet works fine in 2000 but not in 2005. Is this a bug? I
> get a conversion error on the 2005 server. Any ideas?
> Create table Sample
> (DESCR Char(30),
> Ref Char(50),
> Name Char(30))
> Insert into sample
> values ('Valid Row','03/25/2006 02:30:15 PM','Joe')
> Insert into sample
> values ('Something else','Not date related','Jack')
> DECLARE @.Ref_DT datetime
> SELECT @.Ref_DT = '03/25/2006 02:30:15 PM'
> Select Name From Sample
> Where SubString(DESCR, 1, 20) = 'Valid Row'
> and Convert(datetime, REF, 121) = @.Ref_DT
>
The "bug" is that you're trying to convert a non-date value to a
DATETIME. How is this SQL's fault?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||shub wrote:
> This code snippet works fine in 2000 but not in 2005. Is this a bug? I
> get a conversion error on the 2005 server. Any ideas?
>
Evaluation order is not guaranteed in any query so this can't be called
a bug even though it is inconvenient.
In the example given it would anyway be much better to make @.Ref_DT a
CHAR(50) instead of DATETIME. That way you can avoid the conversion for
each row.
If you must use CONVERT then try making a CASE expression of it (watch
out for line wrapping in this example):
...
WHERE SUBSTRING(DESCR, 1, 20) = 'Valid Row'
AND CASE WHEN REF
LIKE '[012][0-9]/[0123][0-9]/[12][0-9][0-9][
0-9]
[012][0-9]:[0-5][0-9]:[0-5][0-9] [AP]M'
THEN CONVERT(DATETIME, REF, 121)
END = @.Ref_DT;
The above isn't foolproof but it does at least force the right
evaluation order (usually). In your case you can also use the ISDATE
function in place of my LIKE expression. ISDATE has the disadvantage
that it depends on implicit conversion so it isn't suitable for all
date formats.
The most important lesson is, don't store dates as strings.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Break statement does not work

I am trying to delete 500000 rows at a time from a big table. I am trying to
loop through it. I am attaching my code, what am I doing wrong. It either
will delete only 500000 rows and exit ot it will keep on looping. I am askin
g
it to delete data older than a certain date. If there are 3000000 rows , I
want it to loop till it deletes 3000000 rows and then exit. Instead if i set
the loop counter to 10, it will loop ten times and then exit. The break
statement does not work
here is the code
declare @.i int
select @.i=10
set rowcount 1000000
while @.i>0
begin
delete from flat_reporttbl
where logdate<'4/1/2004'
select @.i=@.i-1
break
end
thankscheck it.
delete top(10) from flat_reporttbl
where logdate<'4/1/2004'
the problem in ur code is ,
delete statement deletes all rows
which satisfies the condition in
where clause and
then check the while condition.
"batgirl" <batgirl@.discussions.microsoft.com> wrote in message
news:49793BEE-8D40-480E-8D5A-03E77A45A541@.microsoft.com...
>I am trying to delete 500000 rows at a time from a big table. I am trying
>to
> loop through it. I am attaching my code, what am I doing wrong. It either
> will delete only 500000 rows and exit ot it will keep on looping. I am
> asking
> it to delete data older than a certain date. If there are 3000000 rows , I
> want it to loop till it deletes 3000000 rows and then exit. Instead if i
> set
> the loop counter to 10, it will loop ten times and then exit. The break
> statement does not work
> here is the code
> declare @.i int
> select @.i=10
> set rowcount 1000000
> while @.i>0
> begin
> delete from flat_reporttbl
> where logdate<'4/1/2004'
> select @.i=@.i-1
> break
> end
> thanks|||I don't know if we can use the TOP keyword with delete. It gives me an error
message when I try to use it. Also I have already set the rowcount, so does
using TOP help?
batgirl
"batgirl" wrote:

> I am trying to delete 500000 rows at a time from a big table. I am trying
to
> loop through it. I am attaching my code, what am I doing wrong. It either
> will delete only 500000 rows and exit ot it will keep on looping. I am ask
ing
> it to delete data older than a certain date. If there are 3000000 rows , I
> want it to loop till it deletes 3000000 rows and then exit. Instead if i s
et
> the loop counter to 10, it will loop ten times and then exit. The break
> statement does not work
> here is the code
> declare @.i int
> select @.i=10
> set rowcount 1000000
> while @.i>0
> begin
> delete from flat_reporttbl
> where logdate<'4/1/2004'
> select @.i=@.i-1
> break
> end
> thanks

Break Apart Data Using While Loop

I need to break apart the following data into multiple records but I am not
sure how to write the code. The record identifier is the ;
RecordID DataInfo
1 "abc", "def"; "ghi", "jkl"
2 "abc", "def", "ghi"; "jkl", "mno"
Data in a new table will be
RecordID DataInfo
1 "abc", "def"
1 "ghi", "jkl"
2 "abc", "def", "ghi"
2 "jkl", "mno"
Thanks for the help!If the delimiter ; only appears once in the DataInfo column you could
do something like this...
Create Table #TableA
(
Record Integer,
DataInfo varchar(50)
)
Insert Into #TableA
Select 1, '"abc", "def"; "ghi", "jkl"'
Union
Select 2, '"abc", "def", "ghi"; "jkl", "mno" '
Create Table #TableB
(
Record Integer,
DateInfo Varchar(50)
)
Insert Into #TableB
Select Record, Replace((Ltrim(Rtrim(Left(DataInfo,
(CharIndex(';',DataInfo)))))), ';','')
>From #TableA
Union
Select Record, Replace((Ltrim(Rtrim(Right(DataInfo,
(CharIndex(';',Reverse(DataInfo))))))), ';','')
>From #TableA
Select * From #TableA
Select * From #TableB
Drop Table #TableA
Drop Table #TableB
HTH
Barry|||Sorry my example wasn't specific enough. The delimiter can appear multiple
times in the column.
"Barry" wrote:

> If the delimiter ; only appears once in the DataInfo column you could
> do something like this...
>
> Create Table #TableA
> (
> Record Integer,
> DataInfo varchar(50)
> )
> Insert Into #TableA
> Select 1, '"abc", "def"; "ghi", "jkl"'
> Union
> Select 2, '"abc", "def", "ghi"; "jkl", "mno" '
> Create Table #TableB
> (
> Record Integer,
> DateInfo Varchar(50)
> )
>
> Insert Into #TableB
> Select Record, Replace((Ltrim(Rtrim(Left(DataInfo,
> (CharIndex(';',DataInfo)))))), ';','')
> Union
> Select Record, Replace((Ltrim(Rtrim(Right(DataInfo,
> (CharIndex(';',Reverse(DataInfo))))))), ';','')
>
> Select * From #TableA
> Select * From #TableB
> Drop Table #TableA
> Drop Table #TableB
>
> HTH
> Barry
>|||
As an alternative you can use a numbers table
as in http://www.aspfaq.com/show.asp?id=2516
and can do this
select Record,
ltrim(substring(DataInfo,
Number,
charindex(';',
DataInfo + ';',
Number) - Number)) as DataInfo
from #TableA
inner join Numbers on Number between 1 and len(DataInfo) + 1
and substring(';' + DataInfo, Number, 1) = ';'|||Or regular expressions...
http://www.sqlservercentral.com/col...oolkitpart2.asp
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:37F08AA1-FC30-42C9-B50F-50B0FA9FD7B5@.microsoft.com...
>I need to break apart the following data into multiple records but I am not
> sure how to write the code. The record identifier is the ;
> RecordID DataInfo
> 1 "abc", "def"; "ghi", "jkl"
> 2 "abc", "def", "ghi"; "jkl", "mno"
> Data in a new table will be
> RecordID DataInfo
> 1 "abc", "def"
> 1 "ghi", "jkl"
> 2 "abc", "def", "ghi"
> 2 "jkl", "mno"
> Thanks for the help!
>

BPA Feedback

It would be nice to be able to code your own rule libraries to include internal standards etc. Any plans for this?
Hi Rob
We've discussed it and would like to do it but it will not happen in the
initial release. Longer term, definitely.
Thanks,
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:CCA95DE6-4BAF-45AE-893C-EE50BB413810@.microsoft.com...
> It would be nice to be able to code your own rule libraries to include
internal standards etc. Any plans for this?