Hi all!
MSSQL 7.0 and MSSQL2000 Server.
When running query:
create table t (id int primary key identity(1,1), f int, u varchar not null
default USER)
GO
create view v
as
select id, f
from t
where u = USER
GO
insert v (f)
select f
from v
group by f
GO
--
then get error:
Server: Msg 8624, Level 16, State 9, Line 1
Internal SQL Server error.
It's a bug?hi
just try this way
create table t
(id int primary key identity(1,1),
f int, u varchar not null
default 'USER')
GO
create view v
as
select id, f
from t
where u = 'USER'
GO
insert v (f) select f from t group by f
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Roman S. Golubin" wrote:
> Hi all!
> MSSQL 7.0 and MSSQL2000 Server.
> When running query:
> --
> create table t (id int primary key identity(1,1), f int, u varchar not nul
l
> default USER)
> GO
> create view v
> as
> select id, f
> from t
> where u = USER
> GO
> insert v (f)
> select f
> from v
> group by f
> GO
> --
> then get error:
> --
> Server: Msg 8624, Level 16, State 9, Line 1
> Internal SQL Server error.
>
> It's a bug?
>
>|||Hi, Chandra!
> just try this way
> create table t
> (id int primary key identity(1,1),
> f int, u varchar not null
> default 'USER')
> GO
BOL. USER.
-> Use USER to return the current user's database username
Showing posts with label varchar. Show all posts
Showing posts with label varchar. Show all posts
Tuesday, March 20, 2012
BUG: Internal Server Error
Hi all!
MSSQL 7.0 and MSSQL2000 Server.
When running query:
create table t (id int primary key identity(1,1), f int, u varchar not null
default USER)
GO
create view v
as
select id, f
from t
where u = USER
GO
insert v (f)
select f
from v
group by f
GO
then get error:
Server: Msg 8624, Level 16, State 9, Line 1
Internal SQL Server error.
It's a bug?
hi
just try this way
create table t
(id int primary key identity(1,1),
f int, u varchar not null
default 'USER')
GO
create view v
as
select id, f
from t
where u = 'USER'
GO
insert v (f) select f from t group by f
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Roman S. Golubin" wrote:
> Hi all!
> MSSQL 7.0 and MSSQL2000 Server.
> When running query:
> --
> create table t (id int primary key identity(1,1), f int, u varchar not null
> default USER)
> GO
> create view v
> as
> select id, f
> from t
> where u = USER
> GO
> insert v (f)
> select f
> from v
> group by f
> GO
> --
> then get error:
> --
> Server: Msg 8624, Level 16, State 9, Line 1
> Internal SQL Server error.
>
> It's a bug?
>
>
|||Hi, Chandra!
> just try this way
> create table t
> (id int primary key identity(1,1),
> f int, u varchar not null
> default 'USER')
> GO
BOL. USER.
-> Use USER to return the current user's database username
MSSQL 7.0 and MSSQL2000 Server.
When running query:
create table t (id int primary key identity(1,1), f int, u varchar not null
default USER)
GO
create view v
as
select id, f
from t
where u = USER
GO
insert v (f)
select f
from v
group by f
GO
then get error:
Server: Msg 8624, Level 16, State 9, Line 1
Internal SQL Server error.
It's a bug?
hi
just try this way
create table t
(id int primary key identity(1,1),
f int, u varchar not null
default 'USER')
GO
create view v
as
select id, f
from t
where u = 'USER'
GO
insert v (f) select f from t group by f
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Roman S. Golubin" wrote:
> Hi all!
> MSSQL 7.0 and MSSQL2000 Server.
> When running query:
> --
> create table t (id int primary key identity(1,1), f int, u varchar not null
> default USER)
> GO
> create view v
> as
> select id, f
> from t
> where u = USER
> GO
> insert v (f)
> select f
> from v
> group by f
> GO
> --
> then get error:
> --
> Server: Msg 8624, Level 16, State 9, Line 1
> Internal SQL Server error.
>
> It's a bug?
>
>
|||Hi, Chandra!
> just try this way
> create table t
> (id int primary key identity(1,1),
> f int, u varchar not null
> default 'USER')
> GO
BOL. USER.
-> Use USER to return the current user's database username
Monday, March 19, 2012
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?|||HiI 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?|||HiI 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.
|||ThanksI 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?|||HiI 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.
|||ThanksI resolved the problem by upgrading the Sql Server Management Objects from the Nov 2005 release to the Feb 2007 release.
BUG(?): Distinct + variables TSQL
Hi All,
the following TSQL statment doesn't return expected value:
declare @.tmp varchar(500)
set @.tmp = ''
select distinct @.tmp = @.tmp + ',' + Col
from ( select 'A' as Col union all select 'B' union all select 'A' ) x
select @.tmp
expected: ',A,B', returns: ',B'
This bug (?) relates to SQL2000 and SQL2005
Regards
Marcin Zacharzewskideclare @.tmp varchar(500)
set @.tmp = ''
select @.tmp = @.tmp + ',' + Col
from
( select 'A' as Col
union
select 'B'
union
select 'A' ) x
select @.tmp
Hope this helps.
mzacharzewski@.linksoft.pl wrote:
> Hi All,
> the following TSQL statment doesn't return expected value:
> declare @.tmp varchar(500)
> set @.tmp = ''
> select distinct @.tmp = @.tmp + ',' + Col
> from ( select 'A' as Col union all select 'B' union all select 'A' ) x
> select @.tmp
> expected: ',A,B', returns: ',B'
> This bug (?) relates to SQL2000 and SQL2005
> Regards
> Marcin Zacharzewski|||Thank you, for your help,
but I just wanted to warn everybody, that such a bug exists in SQL200x
- In my particular situation I found a different walkaround.
select @.tmp = @.tmp + Col from
( select distinct Col from some_table) x
But this unexpected behaviour caused me some problems with my dynamic
SQL.
Hope MS will patch it in following SPs.
Regards:
Marcin Zacharzewski
gandhimanisha@.gmail.com napisal(a):
> declare @.tmp varchar(500)
> set @.tmp = ''
> select @.tmp = @.tmp + ',' + Col
> from
> ( select 'A' as Col
> union
> select 'B'
> union
> select 'A' ) x
> select @.tmp
> Hope this helps.
>
> mzacharzewski@.linksoft.pl wrote:|||mzacharzewski@.linksoft.pl wrote:
> Thank you, for your help,
> but I just wanted to warn everybody, that such a bug exists in SQL200x
> - In my particular situation I found a different walkaround.
> select @.tmp = @.tmp + Col from
> ( select distinct Col from some_table) x
> But this unexpected behaviour caused me some problems with my dynamic
> SQL.
> Hope MS will patch it in following SPs.
> Regards:
> Marcin Zacharzewski
>
Officially, it's not a bug. The correct result of an assignment in a
SELECT statement that returns multiple rows is undefined, so it's
dangerous to rely on it in any case. In fact Books Online says only
that the "last" value returned should be assigned to the variable.
Arguably therefore the result of the query you posted is correct.
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
--|||>> the following TSQL statment doesn't return expected value:
That is not valid syntax and hence we shouldn't have any expectation.
It is unfortunate that such methods are widely used and even promoted by
some as valid shortcuts to generate concatenated lists of column values from
multiple rows.
Anith|||Thanks David,
I my opinion common sense/programming practices suggest this should
work and shame on MS, it doesn't...
There shouldn't be such an unexpected behaviour - without DISTINCT
everything works great, using DISTINCT SQL Server returns one value -
definitely an error should be raised instead. I still consider this as
a bug.
I used:
SELECT @.var = @.var + col FROM table
syntax very often before - instead of other more complicated/longer
statements and that always worked as expected.
TSQL programmer shouldn't waste his time on checking (books online)
whether sth is possible and will work as expected - he should rely on
his programming practice and errors reported instead.
In my particular case this bug made me lots of problems with a
complicated report - consisting of a few views definitions which are
dynamically constucted based on rules defined in a table..
Regards:
Marcin Zacharzewski ( MCT, MCDBA, MCSD, MCSE)
David Portas napisal(a):
> mzacharzewski@.linksoft.pl wrote:
> Officially, it's not a bug. The correct result of an assignment in a
> SELECT statement that returns multiple rows is undefined, so it's
> dangerous to rely on it in any case. In fact Books Online says only
> that the "last" value returned should be assigned to the variable.
> Arguably therefore the result of the query you posted is correct.
> --
> 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
> --|||mzacharzewski@.linksoft.pl wrote:
> Thanks David,
> I my opinion common sense/programming practices suggest this should
> work and shame on MS, it doesn't...
> There shouldn't be such an unexpected behaviour - without DISTINCT
> everything works great, using DISTINCT SQL Server returns one value -
> definitely an error should be raised instead. I still consider this as
> a bug.
I'll agree that MS should in future disallow these assignments
altogether. BOTH the queries you posted ought to return a syntax error.
It's always seemed obvious to me that there was something faulty about
the @.tmp = @.tmp + ',' + Col syntax in a query with multiple rows. The
result is a string that depends on the evaluation order. But in SQL
there is no way to control the evaluation order so the result is always
going to be unpredictable. Unfortunately, it has turned out that many
people don't find this so obvious. The lesson is that idiosyncratic
"features", however convenient, shouldn't be viewed as a substitute for
good logical design on the part of the developer.
There are some reliable alternatives in SQL Server 2000 and you can
Google for them. In 2005 we have some other options. The following are
proper aggregations (they can be grouped) and you can control the order
of concatenation so as to ensure a deterministic result.
CREATE TABLE tbl (col1 INT NOT NULL, col2 VARCHAR(10) NOT NULL, PRIMARY
KEY (col1,col2));
INSERT INTO tbl (col1,col2) VALUES (1,'ABC');
INSERT INTO tbl (col1,col2) VALUES (1,'DEF');
INSERT INTO tbl (col1,col2) VALUES (2,'GHI');
INSERT INTO tbl (col1,col2) VALUES (2,'JKL');
WITH t AS (
SELECT col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) AS row_no
FROM tbl)
SELECT col1,
MAX(CASE WHEN row_no = 1 THEN col2 END)+
MAX(CASE WHEN row_no = 2 THEN ','+col2 ELSE '' END)+
MAX(CASE WHEN row_no = 3 THEN ','+col2 ELSE '' END)+
MAX(CASE WHEN row_no = 4 THEN ','+col2 ELSE '' END)
FROM t
GROUP BY col1 ;
SELECT DISTINCT col1,
SUBSTRING(
(SELECT ','+col2 AS [text()]
FROM tbl
WHERE col1 = T.col1
ORDER BY col2
FOR XML PATH( '' )
), 2,100) AS concat
FROM tbl AS T
ORDER BY col1 ;
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
--|||(mzacharzewski@.linksoft.pl) writes:
> I my opinion common sense/programming practices suggest this should
> work and shame on MS, it doesn't...
> There shouldn't be such an unexpected behaviour - without DISTINCT
> everything works great, using DISTINCT SQL Server returns one value -
> definitely an error should be raised instead. I still consider this as
> a bug.
No matter what your opinion is, it is not a bug, just undefined behaviour.
> I used:
> SELECT @.var = @.var + col FROM table
> syntax very often before - instead of other more complicated/longer
> statements and that always worked as expected.
> TSQL programmer shouldn't waste his time on checking (books online)
Here I must take strong exception. If you think that reading Books
Online is a waste of time, then you have a serious problem.
> whether sth is possible and will work as expected - he should rely on
> his programming practice and errors reported instead.
Relying on programming practice can lead you seriously astray. Consider
this statment:
SELECT ...
FROM tbl
WHERE b <> 0
AND a/b > 1
A programer who is new to SQL but have done a lot of C/C++ following
his programming practice only would gladly assume this would shortcut
and be safe. But he would be very wrong on that point, because SQL does
not shortcut.
Different language has different practices, and a good prorgammer must
check the documentation for the tool he is currently using.
But I can agree that it would be a good thing if SELECT @.x = @.x + col FROM
produced a warning that you are on dangerous grounds. Or even produced an
error (depending on compatibility level). Or simply the behaviour would be
the one that everyone expects.
(The correct way of coding the above is:
SELECT ...
FROM tbl
WHERE CASE WHEN b <> 0 THEN a/b END > 1
)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
I can agree only with your last sentence:
>Erland Sommarskog napisal(a):
>...
> But I can agree that it would be a good thing if SELECT @.x = @.x + col FROM
> produced a warning that you are on dangerous grounds. Or even produced an
> error (depending on compatibility level). Or simply the behaviour would be
> the one that everyone expects.
thats what I meant - the problem is that SQL allows such a
sensible-looking statements and doesn't raise an error/warning.
You misunderstood me - reading SQL Books online is not a waste of time
- I use this great help really often, but I think a programmer
shouldn't waste his time bothering (and digging BOL) whether a
sensible-looking statement is valid - especially considering that:
select @.tmp = @.tmp + col from table
works well in any scenario, and I've been using this for years.
My programming rule tells me:
everythnig that is not an error/warning, and looks-sensible - is
allowed and should give definend/predicitble result.
BTW I don't understand your examples, WHERE clause is evaluated first,
so both your queries are allowed:
select * from table where b<>0 and a/b > 1
and
select * from table where CASE WHEN b <> 0 THEN a/b END > 1
in this particular example, the first one is (IMHO) even better:)
Of course you have to use CASE in order to get a/b value in SELECT
clause:
select CASE WHEN b <> 0 THEN a/b END from table
but I am sure you know it.
Regards:
Marcin Zacharzewski ( MCT, MCDBA, MCSD, MCSE)
Erland Sommarskog napisal(a):
> (mzacharzewski@.linksoft.pl) writes:
> No matter what your opinion is, it is not a bug, just undefined behaviour.|||mzacharzewski@.linksoft.pl wrote:
> I think a programmer
> shouldn't waste his time bothering (and digging BOL) whether a
> sensible-looking statement is valid - especially considering that:
> select @.tmp = @.tmp + col from table
> works well in any scenario, and I've been using this for years.
> My programming rule tells me:
> everythnig that is not an error/warning, and looks-sensible - is
> allowed and should give definend/predicitble result.
How can a string concatentation possibly give a defined and predictable
result if the concatenation order is not specified? Maybe you think
that if you include ORDER BY it will affect the result. But ORDER BY
only applies to multiple row result sets, not to the execution order of
a query. So while it may *seem* to work for you (most of the time),
this undocumented curiosity is just that. It isn't sensible at all in
my book and I would generally recommend you avoid it.
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
--
the following TSQL statment doesn't return expected value:
declare @.tmp varchar(500)
set @.tmp = ''
select distinct @.tmp = @.tmp + ',' + Col
from ( select 'A' as Col union all select 'B' union all select 'A' ) x
select @.tmp
expected: ',A,B', returns: ',B'
This bug (?) relates to SQL2000 and SQL2005
Regards
Marcin Zacharzewskideclare @.tmp varchar(500)
set @.tmp = ''
select @.tmp = @.tmp + ',' + Col
from
( select 'A' as Col
union
select 'B'
union
select 'A' ) x
select @.tmp
Hope this helps.
mzacharzewski@.linksoft.pl wrote:
> Hi All,
> the following TSQL statment doesn't return expected value:
> declare @.tmp varchar(500)
> set @.tmp = ''
> select distinct @.tmp = @.tmp + ',' + Col
> from ( select 'A' as Col union all select 'B' union all select 'A' ) x
> select @.tmp
> expected: ',A,B', returns: ',B'
> This bug (?) relates to SQL2000 and SQL2005
> Regards
> Marcin Zacharzewski|||Thank you, for your help,
but I just wanted to warn everybody, that such a bug exists in SQL200x
- In my particular situation I found a different walkaround.
select @.tmp = @.tmp + Col from
( select distinct Col from some_table) x
But this unexpected behaviour caused me some problems with my dynamic
SQL.
Hope MS will patch it in following SPs.
Regards:
Marcin Zacharzewski
gandhimanisha@.gmail.com napisal(a):
> declare @.tmp varchar(500)
> set @.tmp = ''
> select @.tmp = @.tmp + ',' + Col
> from
> ( select 'A' as Col
> union
> select 'B'
> union
> select 'A' ) x
> select @.tmp
> Hope this helps.
>
> mzacharzewski@.linksoft.pl wrote:|||mzacharzewski@.linksoft.pl wrote:
> Thank you, for your help,
> but I just wanted to warn everybody, that such a bug exists in SQL200x
> - In my particular situation I found a different walkaround.
> select @.tmp = @.tmp + Col from
> ( select distinct Col from some_table) x
> But this unexpected behaviour caused me some problems with my dynamic
> SQL.
> Hope MS will patch it in following SPs.
> Regards:
> Marcin Zacharzewski
>
Officially, it's not a bug. The correct result of an assignment in a
SELECT statement that returns multiple rows is undefined, so it's
dangerous to rely on it in any case. In fact Books Online says only
that the "last" value returned should be assigned to the variable.
Arguably therefore the result of the query you posted is correct.
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
--|||>> the following TSQL statment doesn't return expected value:
That is not valid syntax and hence we shouldn't have any expectation.
It is unfortunate that such methods are widely used and even promoted by
some as valid shortcuts to generate concatenated lists of column values from
multiple rows.
Anith|||Thanks David,
I my opinion common sense/programming practices suggest this should
work and shame on MS, it doesn't...
There shouldn't be such an unexpected behaviour - without DISTINCT
everything works great, using DISTINCT SQL Server returns one value -
definitely an error should be raised instead. I still consider this as
a bug.
I used:
SELECT @.var = @.var + col FROM table
syntax very often before - instead of other more complicated/longer
statements and that always worked as expected.
TSQL programmer shouldn't waste his time on checking (books online)
whether sth is possible and will work as expected - he should rely on
his programming practice and errors reported instead.
In my particular case this bug made me lots of problems with a
complicated report - consisting of a few views definitions which are
dynamically constucted based on rules defined in a table..
Regards:
Marcin Zacharzewski ( MCT, MCDBA, MCSD, MCSE)
David Portas napisal(a):
> mzacharzewski@.linksoft.pl wrote:
> Officially, it's not a bug. The correct result of an assignment in a
> SELECT statement that returns multiple rows is undefined, so it's
> dangerous to rely on it in any case. In fact Books Online says only
> that the "last" value returned should be assigned to the variable.
> Arguably therefore the result of the query you posted is correct.
> --
> 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
> --|||mzacharzewski@.linksoft.pl wrote:
> Thanks David,
> I my opinion common sense/programming practices suggest this should
> work and shame on MS, it doesn't...
> There shouldn't be such an unexpected behaviour - without DISTINCT
> everything works great, using DISTINCT SQL Server returns one value -
> definitely an error should be raised instead. I still consider this as
> a bug.
I'll agree that MS should in future disallow these assignments
altogether. BOTH the queries you posted ought to return a syntax error.
It's always seemed obvious to me that there was something faulty about
the @.tmp = @.tmp + ',' + Col syntax in a query with multiple rows. The
result is a string that depends on the evaluation order. But in SQL
there is no way to control the evaluation order so the result is always
going to be unpredictable. Unfortunately, it has turned out that many
people don't find this so obvious. The lesson is that idiosyncratic
"features", however convenient, shouldn't be viewed as a substitute for
good logical design on the part of the developer.
There are some reliable alternatives in SQL Server 2000 and you can
Google for them. In 2005 we have some other options. The following are
proper aggregations (they can be grouped) and you can control the order
of concatenation so as to ensure a deterministic result.
CREATE TABLE tbl (col1 INT NOT NULL, col2 VARCHAR(10) NOT NULL, PRIMARY
KEY (col1,col2));
INSERT INTO tbl (col1,col2) VALUES (1,'ABC');
INSERT INTO tbl (col1,col2) VALUES (1,'DEF');
INSERT INTO tbl (col1,col2) VALUES (2,'GHI');
INSERT INTO tbl (col1,col2) VALUES (2,'JKL');
WITH t AS (
SELECT col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) AS row_no
FROM tbl)
SELECT col1,
MAX(CASE WHEN row_no = 1 THEN col2 END)+
MAX(CASE WHEN row_no = 2 THEN ','+col2 ELSE '' END)+
MAX(CASE WHEN row_no = 3 THEN ','+col2 ELSE '' END)+
MAX(CASE WHEN row_no = 4 THEN ','+col2 ELSE '' END)
FROM t
GROUP BY col1 ;
SELECT DISTINCT col1,
SUBSTRING(
(SELECT ','+col2 AS [text()]
FROM tbl
WHERE col1 = T.col1
ORDER BY col2
FOR XML PATH( '' )
), 2,100) AS concat
FROM tbl AS T
ORDER BY col1 ;
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
--|||(mzacharzewski@.linksoft.pl) writes:
> I my opinion common sense/programming practices suggest this should
> work and shame on MS, it doesn't...
> There shouldn't be such an unexpected behaviour - without DISTINCT
> everything works great, using DISTINCT SQL Server returns one value -
> definitely an error should be raised instead. I still consider this as
> a bug.
No matter what your opinion is, it is not a bug, just undefined behaviour.
> I used:
> SELECT @.var = @.var + col FROM table
> syntax very often before - instead of other more complicated/longer
> statements and that always worked as expected.
> TSQL programmer shouldn't waste his time on checking (books online)
Here I must take strong exception. If you think that reading Books
Online is a waste of time, then you have a serious problem.
> whether sth is possible and will work as expected - he should rely on
> his programming practice and errors reported instead.
Relying on programming practice can lead you seriously astray. Consider
this statment:
SELECT ...
FROM tbl
WHERE b <> 0
AND a/b > 1
A programer who is new to SQL but have done a lot of C/C++ following
his programming practice only would gladly assume this would shortcut
and be safe. But he would be very wrong on that point, because SQL does
not shortcut.
Different language has different practices, and a good prorgammer must
check the documentation for the tool he is currently using.
But I can agree that it would be a good thing if SELECT @.x = @.x + col FROM
produced a warning that you are on dangerous grounds. Or even produced an
error (depending on compatibility level). Or simply the behaviour would be
the one that everyone expects.
(The correct way of coding the above is:
SELECT ...
FROM tbl
WHERE CASE WHEN b <> 0 THEN a/b END > 1
)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
I can agree only with your last sentence:
>Erland Sommarskog napisal(a):
>...
> But I can agree that it would be a good thing if SELECT @.x = @.x + col FROM
> produced a warning that you are on dangerous grounds. Or even produced an
> error (depending on compatibility level). Or simply the behaviour would be
> the one that everyone expects.
thats what I meant - the problem is that SQL allows such a
sensible-looking statements and doesn't raise an error/warning.
You misunderstood me - reading SQL Books online is not a waste of time
- I use this great help really often, but I think a programmer
shouldn't waste his time bothering (and digging BOL) whether a
sensible-looking statement is valid - especially considering that:
select @.tmp = @.tmp + col from table
works well in any scenario, and I've been using this for years.
My programming rule tells me:
everythnig that is not an error/warning, and looks-sensible - is
allowed and should give definend/predicitble result.
BTW I don't understand your examples, WHERE clause is evaluated first,
so both your queries are allowed:
select * from table where b<>0 and a/b > 1
and
select * from table where CASE WHEN b <> 0 THEN a/b END > 1
in this particular example, the first one is (IMHO) even better:)
Of course you have to use CASE in order to get a/b value in SELECT
clause:
select CASE WHEN b <> 0 THEN a/b END from table
but I am sure you know it.
Regards:
Marcin Zacharzewski ( MCT, MCDBA, MCSD, MCSE)
Erland Sommarskog napisal(a):
> (mzacharzewski@.linksoft.pl) writes:
> No matter what your opinion is, it is not a bug, just undefined behaviour.|||mzacharzewski@.linksoft.pl wrote:
> I think a programmer
> shouldn't waste his time bothering (and digging BOL) whether a
> sensible-looking statement is valid - especially considering that:
> select @.tmp = @.tmp + col from table
> works well in any scenario, and I've been using this for years.
> My programming rule tells me:
> everythnig that is not an error/warning, and looks-sensible - is
> allowed and should give definend/predicitble result.
How can a string concatentation possibly give a defined and predictable
result if the concatenation order is not specified? Maybe you think
that if you include ORDER BY it will affect the result. But ORDER BY
only applies to multiple row result sets, not to the execution order of
a query. So while it may *seem* to work for you (most of the time),
this undocumented curiosity is just that. It isn't sensible at all in
my book and I would generally recommend you avoid it.
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
--
Sunday, March 11, 2012
Bug in the OLE DB command?
This command has no error:
update art_anz set anz = anz where artnr = 'xxxxxxx'
declare @.artnr as varchar(10)
set @.artnr = ?
But this command has an error:
--update art_anz set anz = anz where artnr = 'xxxxxxx'
declare @.artnr as varchar(10)
set @.artnr = ?
The difference is the first line. When you use parameters (?) in the OLE DB-Command, the very first line has to be a Non-Select SQL-Statement.
The SQL-Statement do nothing!
When you have no parameters, you can write normal T-SQL-Code and you get no error.
I think, this is a bug!!
It certainly sounds like it could be a bug. Could you log it at the feedback centre: http://lab.msdn.microsoft.com/productfeedback/default.aspx
-Jamie
Bug in SSIS?
I am trying to convert a string to a specific date format by using a simple select like this:
SELECT CONVERT(VARCHAR(3), CONVERT(DATETIME, ?, 103),107)
First of all the Parameter ? is not recognized and when i run the Preview it would trought the following error even if i change the ? with '20050101' for example:
===================================
There was an error displaying the preview. (Microsoft Visual Studio)
===================================
Undefined function 'CONVERT' in expression. (Microsoft JET Database Engine)
Program Location:
at Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.Connections.SQLTaskConnectionOleDbClass.ExecuteStatement(Int32 resultType, Boolean isStoredProc, UInt32 dwTimeOut)
at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, ConnectionManager connectionManager, Control parentWindow, IServiceProvider serviceProvider, IDTSExternalMetadataColumnCollection90 externalColumns)
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowConnectionPage.previewButton_Click(Object sender, EventArgs e)
Anyone experiencing this kind of problem? Solutions?
Best Regards,
Luis Sim?es
Well the error clearly indicates that Jet doesn't support the function so this would not be an SSIS bug. Have you attempted to look at the Jet docs to see if it supports convert. The last time I used jet it did not.
Thanks,
Matt
|||Yes you are right.
I have figured it out pretty quickly and i tried to delete this post without success sorry...
It was my mistake! Not really looking into it :P
But thanks :)
Best Regards,
PS: Merry Christmas to All of YOU!
Thursday, March 8, 2012
Bug in Query Analyzer
Execute this in Query Analyzer:
SELECT CONVERT(varchar(8),0x0131) as A, 'X' as B
You will get the expected results if you choose "Results in text", but
if you choose "Results in grid" you will get nothing in column A and
'1' in column B.
Is there anyone at MS reading this ?
Is there any other place I should report this bug ?
Razvan Socol
I can report this as a bug. It does appear taht the grid does not return
the correct information, or atleast does not interpret it correctly.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||This inserts two characters into A: 0x01 and 0x31. 0x31 is, of course, an
ASCII '1'.
Now you know that 0x01 is the character that QA uses to separate columns
into the grid.
So, the grid has two columns defined by the select, but when populating the
columns QA sees three columns of data: NULL, '1', and 'X'. This causes the
final column of data not to have a home.
So, is this a bug? If Microsoft is willing to think so, that is great, but
I would consider this a behavior instead of a bug.
Russell Fields
"Razvan Socol" <rsocol@.fx.ro> wrote in message
news:60f52b8b.0406090141.7a9f61d7@.posting.google.c om...
> Execute this in Query Analyzer:
> SELECT CONVERT(varchar(8),0x0131) as A, 'X' as B
> You will get the expected results if you choose "Results in text", but
> if you choose "Results in grid" you will get nothing in column A and
> '1' in column B.
> Is there anyone at MS reading this ?
> Is there any other place I should report this bug ?
> Razvan Socol
|||I definitely think that it's a bug. I specifically simplified the
query to this form (the situation I encountered was more complex). I
understood all that you explained before I posted, but it's good that
you did explain it, so that other readers understand.
Razvan
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote:
> This inserts two characters into A: 0x01 and 0x31. 0x31 is, of course, an
> ASCII '1'.
> Now you know that 0x01 is the character that QA uses to separate columns
> into the grid.
> So, the grid has two columns defined by the select, but when populating the
> columns QA sees three columns of data: NULL, '1', and 'X'. This causes the
> final column of data not to have a home.
> So, is this a bug? If Microsoft is willing to think so, that is great, but
> I would consider this a behavior instead of a bug.
> Russell Fields
SELECT CONVERT(varchar(8),0x0131) as A, 'X' as B
You will get the expected results if you choose "Results in text", but
if you choose "Results in grid" you will get nothing in column A and
'1' in column B.
Is there anyone at MS reading this ?
Is there any other place I should report this bug ?
Razvan Socol
I can report this as a bug. It does appear taht the grid does not return
the correct information, or atleast does not interpret it correctly.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||This inserts two characters into A: 0x01 and 0x31. 0x31 is, of course, an
ASCII '1'.
Now you know that 0x01 is the character that QA uses to separate columns
into the grid.
So, the grid has two columns defined by the select, but when populating the
columns QA sees three columns of data: NULL, '1', and 'X'. This causes the
final column of data not to have a home.
So, is this a bug? If Microsoft is willing to think so, that is great, but
I would consider this a behavior instead of a bug.
Russell Fields
"Razvan Socol" <rsocol@.fx.ro> wrote in message
news:60f52b8b.0406090141.7a9f61d7@.posting.google.c om...
> Execute this in Query Analyzer:
> SELECT CONVERT(varchar(8),0x0131) as A, 'X' as B
> You will get the expected results if you choose "Results in text", but
> if you choose "Results in grid" you will get nothing in column A and
> '1' in column B.
> Is there anyone at MS reading this ?
> Is there any other place I should report this bug ?
> Razvan Socol
|||I definitely think that it's a bug. I specifically simplified the
query to this form (the situation I encountered was more complex). I
understood all that you explained before I posted, but it's good that
you did explain it, so that other readers understand.
Razvan
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote:
> This inserts two characters into A: 0x01 and 0x31. 0x31 is, of course, an
> ASCII '1'.
> Now you know that 0x01 is the character that QA uses to separate columns
> into the grid.
> So, the grid has two columns defined by the select, but when populating the
> columns QA sees three columns of data: NULL, '1', and 'X'. This causes the
> final column of data not to have a home.
> So, is this a bug? If Microsoft is willing to think so, that is great, but
> I would consider this a behavior instead of a bug.
> Russell Fields
Bug in INFORMATION_SCHEMA?
We just confirmed that if you change the length of a varchar in a table,
INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
for views that use that varchar. You have to drop and recreate the view for
the update to occur. This is a bug in my opinion especially since MS insits
you use the views and not the underlying sys tables. We use a C# class
generator that creates code based on those values and ran into this problem.
Anyone else noticed this?
</joel>That's not a bug -- the problem is that altering a table does not alter the
view that references the table. For instance, you can alter a table and
drop a column, and the view will not be affected -- you'll find out next
time you query it, though!
One way to keep problems like this from happening is to use the WITH
SCHEMABINDING option. This will disallow changes to the base table unless
you drop the view -- meaning that the view will have to be recreated after
you alter the table, and the INFORMATION_SCHEMA will therefore get updated.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> We just confirmed that if you change the length of a varchar in a table,
> INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
> for views that use that varchar. You have to drop and recreate the view
for
> the update to occur. This is a bug in my opinion especially since MS
insits
> you use the views and not the underlying sys tables. We use a C# class
> generator that creates code based on those values and ran into this
problem.
> Anyone else noticed this?
> </joel>
>|||When you change an underlying table, view metadata (in system tables) is not
automatically updated. You need to execute sp_refreshview or ALTER or
DROP/CREATE the view.
Note that this affects not only the data returned by the INFORMATION_SCHEMA
views but also the view itself. You will get old column definitions until
the metadata is refreshed. Illustrative script:
CREATE TABLE Table1(Col1 varchar(10))
GO
CREATE VIEW View1 AS SELECT Col1 FROM Table1
GO
EXEC sp_help 'View1' -- length 10
ALTER TABLE Table1 ALTER COLUMN Col1 varchar(20)
EXEC sp_help 'View1' -- length 10
EXEC sp_refreshview 'View1'
EXEC sp_help 'View1' -- length 20
GO
We follow a standard practice of executing sp_refreshview against all views
following schema changes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> We just confirmed that if you change the length of a varchar in a table,
> INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
> for views that use that varchar. You have to drop and recreate the view
> for the update to occur. This is a bug in my opinion especially since MS
> insits you use the views and not the underlying sys tables. We use a C#
> class generator that creates code based on those values and ran into this
> problem.
> Anyone else noticed this?
> </joel>
>|||That's not a bug in the INFORMATION_SCHEMA really. The metadata about the
columns that are in a view are stored with the view definition and are not
automatically updated when you change the underlying table(s). (This is a
one of the reasons why it is no good idea to use SELECT * in a view).
You don't have to drop and recreate the view for the metadata to be updated
though, you can achieve that with:
EXEC sp_refreshview '<view_name>'
Jacco Schalkwijk
SQL Server MVP
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> We just confirmed that if you change the length of a varchar in a table,
> INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
> for views that use that varchar. You have to drop and recreate the view
> for the update to occur. This is a bug in my opinion especially since MS
> insits you use the views and not the underlying sys tables. We use a C#
> class generator that creates code based on those values and ran into this
> problem.
> Anyone else noticed this?
> </joel>
>|||> for views that use that varchar. You have to drop and recreate the view
for
> the update to occur.
Are you changing the definition of the view, or the underlying table? If
you create your view with SCHEMABINDING, you won't be able to change the
underlying table, and this won't be an issue. Otherwise, plan to run
sp_refreshview on all views that reference the table in order to update the
metadata (this applies both to
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH and syscolumns.length).
Books Online documents this quite well, in fact:
"Refreshes the metadata for the specified view. Persistent metadata for a
view can become outdated because of changes to the underlying objects upon
which the view depends."
While it would be a nice enhancement for the metadata to truly reflect the
current state, I don't think Microsoft will consider it a bug because the
behavior is documented. I also think it would be quite expensive for SQL
Server to watch for ALTER TABLE events and then go and parse all the views
that reference it and make sure their metadata is updated also...
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Wow,
DBAs! :-)
I can certainly see in the case of dropping a column that SqlServer wouldn't
parse a View and try to figure out the program's intent, but a column length
is a column length.
If it walks like a bug...
</joel>
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uWIIeoWKFHA.4028@.tk2msftngp13.phx.gbl...
> That's not a bug -- the problem is that altering a table does not alter
> the
> view that references the table. For instance, you can alter a table and
> drop a column, and the view will not be affected -- you'll find out next
> time you query it, though!
> One way to keep problems like this from happening is to use the WITH
> SCHEMABINDING option. This will disallow changes to the base table unless
> you drop the view -- meaning that the view will have to be recreated after
> you alter the table, and the INFORMATION_SCHEMA will therefore get
> updated.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Joel" <joelycat@.hotmail.com> wrote in message
> news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> for
> insits
> problem.
>|||You found that response "Angry".
You haven't been in this newsgroup very long, have you?
Don't take your frustration out on people answering your questions just
because you don't like the answer.
If you don't like how SQL server handles this send your request to
Microsoft.
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23GqmeCXKFHA.2800@.TK2MSFTNGP10.phx.gbl...
> Wow, DBAs! :-)
> I can certainly see in the case of dropping a column that SqlServer
> wouldn't parse a View and try to figure out the program's intent, but a
> column length is a column length.
> If it walks like a bug...
> </joel>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uWIIeoWKFHA.4028@.tk2msftngp13.phx.gbl...
>|||> Wow, DBAs! :-)
Who's?
> If it walks like a bug...
Again, a bug to you is not a bug to everybody. This behavior is well-known
and well-documented.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oqfel%23XKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Who's?
I'm that someone would imply that I am .
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
for views that use that varchar. You have to drop and recreate the view for
the update to occur. This is a bug in my opinion especially since MS insits
you use the views and not the underlying sys tables. We use a C# class
generator that creates code based on those values and ran into this problem.
Anyone else noticed this?
</joel>That's not a bug -- the problem is that altering a table does not alter the
view that references the table. For instance, you can alter a table and
drop a column, and the view will not be affected -- you'll find out next
time you query it, though!
One way to keep problems like this from happening is to use the WITH
SCHEMABINDING option. This will disallow changes to the base table unless
you drop the view -- meaning that the view will have to be recreated after
you alter the table, and the INFORMATION_SCHEMA will therefore get updated.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> We just confirmed that if you change the length of a varchar in a table,
> INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
> for views that use that varchar. You have to drop and recreate the view
for
> the update to occur. This is a bug in my opinion especially since MS
insits
> you use the views and not the underlying sys tables. We use a C# class
> generator that creates code based on those values and ran into this
problem.
> Anyone else noticed this?
> </joel>
>|||When you change an underlying table, view metadata (in system tables) is not
automatically updated. You need to execute sp_refreshview or ALTER or
DROP/CREATE the view.
Note that this affects not only the data returned by the INFORMATION_SCHEMA
views but also the view itself. You will get old column definitions until
the metadata is refreshed. Illustrative script:
CREATE TABLE Table1(Col1 varchar(10))
GO
CREATE VIEW View1 AS SELECT Col1 FROM Table1
GO
EXEC sp_help 'View1' -- length 10
ALTER TABLE Table1 ALTER COLUMN Col1 varchar(20)
EXEC sp_help 'View1' -- length 10
EXEC sp_refreshview 'View1'
EXEC sp_help 'View1' -- length 20
GO
We follow a standard practice of executing sp_refreshview against all views
following schema changes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> We just confirmed that if you change the length of a varchar in a table,
> INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
> for views that use that varchar. You have to drop and recreate the view
> for the update to occur. This is a bug in my opinion especially since MS
> insits you use the views and not the underlying sys tables. We use a C#
> class generator that creates code based on those values and ran into this
> problem.
> Anyone else noticed this?
> </joel>
>|||That's not a bug in the INFORMATION_SCHEMA really. The metadata about the
columns that are in a view are stored with the view definition and are not
automatically updated when you change the underlying table(s). (This is a
one of the reasons why it is no good idea to use SELECT * in a view).
You don't have to drop and recreate the view for the metadata to be updated
though, you can achieve that with:
EXEC sp_refreshview '<view_name>'
Jacco Schalkwijk
SQL Server MVP
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> We just confirmed that if you change the length of a varchar in a table,
> INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
> for views that use that varchar. You have to drop and recreate the view
> for the update to occur. This is a bug in my opinion especially since MS
> insits you use the views and not the underlying sys tables. We use a C#
> class generator that creates code based on those values and ran into this
> problem.
> Anyone else noticed this?
> </joel>
>|||> for views that use that varchar. You have to drop and recreate the view
for
> the update to occur.
Are you changing the definition of the view, or the underlying table? If
you create your view with SCHEMABINDING, you won't be able to change the
underlying table, and this won't be an issue. Otherwise, plan to run
sp_refreshview on all views that reference the table in order to update the
metadata (this applies both to
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH and syscolumns.length).
Books Online documents this quite well, in fact:
"Refreshes the metadata for the specified view. Persistent metadata for a
view can become outdated because of changes to the underlying objects upon
which the view depends."
While it would be a nice enhancement for the metadata to truly reflect the
current state, I don't think Microsoft will consider it a bug because the
behavior is documented. I also think it would be quite expensive for SQL
Server to watch for ALTER TABLE events and then go and parse all the views
that reference it and make sure their metadata is updated also...
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Wow,
DBAs! :-)I can certainly see in the case of dropping a column that SqlServer wouldn't
parse a View and try to figure out the program's intent, but a column length
is a column length.
If it walks like a bug...
</joel>
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uWIIeoWKFHA.4028@.tk2msftngp13.phx.gbl...
> That's not a bug -- the problem is that altering a table does not alter
> the
> view that references the table. For instance, you can alter a table and
> drop a column, and the view will not be affected -- you'll find out next
> time you query it, though!
> One way to keep problems like this from happening is to use the WITH
> SCHEMABINDING option. This will disallow changes to the base table unless
> you drop the view -- meaning that the view will have to be recreated after
> you alter the table, and the INFORMATION_SCHEMA will therefore get
> updated.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Joel" <joelycat@.hotmail.com> wrote in message
> news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> for
> insits
> problem.
>|||You found that response "Angry".
You haven't been in this newsgroup very long, have you?
Don't take your frustration out on people answering your questions just
because you don't like the answer.
If you don't like how SQL server handles this send your request to
Microsoft.
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23GqmeCXKFHA.2800@.TK2MSFTNGP10.phx.gbl...
> Wow,
DBAs! :-)> I can certainly see in the case of dropping a column that SqlServer
> wouldn't parse a View and try to figure out the program's intent, but a
> column length is a column length.
> If it walks like a bug...
> </joel>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uWIIeoWKFHA.4028@.tk2msftngp13.phx.gbl...
>|||> Wow,
DBAs! :-)Who's
?> If it walks like a bug...
Again, a bug to you is not a bug to everybody. This behavior is well-known
and well-documented.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oqfel%23XKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Who's
?I'm
that someone would imply that I am .Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
Tuesday, February 14, 2012
Breaking up a string column into multiple records
Hopefully someone can help me. I have a table of records with the following fields:
AnswerID (int)
MultipleChoiceMultipleAnswer (varchar)
QuestionID (int)
now the multipleChoiceMultipleAnswer field data is in the following format:
1234,5678,99867
2345,456,7891
I want to break that field up into multiple records using the same AnswerID and QuestionID for each new multiple choice answer, so it would look like this:
10001 1234 9999
10001 5678 9999
10001 99867 9999
10002 2345 9998
10002 456 9998
10002 7891 9998
Is there an optimized method of doing this without using a cursor to iterate through each record?
Any help would be greatly appreciated.
ThanksLook at the following link - if you need something else, let me know -
link (http://dbforums.com/showthread.php?threadid=586248)
AnswerID (int)
MultipleChoiceMultipleAnswer (varchar)
QuestionID (int)
now the multipleChoiceMultipleAnswer field data is in the following format:
1234,5678,99867
2345,456,7891
I want to break that field up into multiple records using the same AnswerID and QuestionID for each new multiple choice answer, so it would look like this:
10001 1234 9999
10001 5678 9999
10001 99867 9999
10002 2345 9998
10002 456 9998
10002 7891 9998
Is there an optimized method of doing this without using a cursor to iterate through each record?
Any help would be greatly appreciated.
ThanksLook at the following link - if you need something else, let me know -
link (http://dbforums.com/showthread.php?threadid=586248)
Breaking a nested trigger
Hi,
I have the following 2 tables:
tbl_CustomerEmail1
(
CustomerID1 int,
EmailAddress varchar(200),
Unsubscribe bit
)
tbl_CustomerEmail2
(
CustomerID2 int,
EmailAddress varchar(200),
Unsubscribe bit
)
On each table I have a trigger to subscribe/unsubscribe a customer in the
other table if it exists:
CREATE trigger trg_UnsubscribeCustomer1 on tbl_Customer2
for update as
declare @.unsubscribe tinyint
declare @.EMail varchar(200)
select @.unsubscribe = unsubscribe, @.Email = EmailAddress
from inserted
if update (unsubscribe)
begin
if exists (select email from tbl_Customer1 where EmailAddress = @.Email)
begin
update tbl_Customer1
set Unsubscribe = @.Unsubscribe
where EmailAddress = @.Email
return
end
end
and:
CREATE trigger trg_UnsubscribeCustomer2 on tbl_Customer1
for update as
declare @.unsubscribe tinyint
declare @.EMail varchar(200)
select @.unsubscribe = unsubscribe, @.Email = EmailAddress
from inserted
if update (unsubscribe)
begin
if exists (select email from tbl_Customer2 where EmailAddress = @.Email)
begin
update tbl_Customer2
set Unsubscribe = @.Unsubscribe
where EmailAddress = @.Email
return
end
end
These 2 triggers fire each other to the nested limit (32) if I try to
subscribe/unsubscribe a customer which exists in both tables yet I can't
disallow the nested trigger property on the server because it may break othe
r
applications in other DB's. The return statement used doesn't work. Is there
a way I can do this without disabling the nested trigger property on the
whole server?
Many thanks for your help.Elisabeth,
If the updates that fire these triggers are never
called from yet other triggers, you should be able
to handle this by putting the following line of code
at the very beginning of each trigger:
if trigger_nestlevel() > 2 return
A better solution is probably to redesign these tables
so that there is no need to store the same information
in two separate places, but for now, using the system
function trigger_nestlevel() may take care of things.
Steve Kass
Drew University
Elisabeth wrote:
>Hi,
>I have the following 2 tables:
>tbl_CustomerEmail1
>(
>CustomerID1 int,
>EmailAddress varchar(200),
>Unsubscribe bit
> )
>tbl_CustomerEmail2
>(
>CustomerID2 int,
>EmailAddress varchar(200),
>Unsubscribe bit
> )
>On each table I have a trigger to subscribe/unsubscribe a customer in the
>other table if it exists:
>
>CREATE trigger trg_UnsubscribeCustomer1 on tbl_Customer2
>for update as
>
>declare @.unsubscribe tinyint
>declare @.EMail varchar(200)
>select @.unsubscribe = unsubscribe, @.Email = EmailAddress
>from inserted
>if update (unsubscribe)
>begin
> if exists (select email from tbl_Customer1 where EmailAddress = @.Email)
> begin
> update tbl_Customer1
> set Unsubscribe = @.Unsubscribe
> where EmailAddress = @.Email
> return
> end
>end
>and:
>
>CREATE trigger trg_UnsubscribeCustomer2 on tbl_Customer1
>for update as
>
>declare @.unsubscribe tinyint
>declare @.EMail varchar(200)
>select @.unsubscribe = unsubscribe, @.Email = EmailAddress
>from inserted
>if update (unsubscribe)
>begin
> if exists (select email from tbl_Customer2 where EmailAddress = @.Email)
> begin
> update tbl_Customer2
> set Unsubscribe = @.Unsubscribe
> where EmailAddress = @.Email
> return
> end
>end
>
>These 2 triggers fire each other to the nested limit (32) if I try to
>subscribe/unsubscribe a customer which exists in both tables yet I can't
>disallow the nested trigger property on the server because it may break oth
er
>applications in other DB's. The return statement used doesn't work. Is ther
e
>a way I can do this without disabling the nested trigger property on the
>whole server?
>Many thanks for your help.
>
>
>|||worked a treat! cheers mate.
"Steve Kass" wrote:
> Elisabeth,
> If the updates that fire these triggers are never
> called from yet other triggers, you should be able
> to handle this by putting the following line of code
> at the very beginning of each trigger:
> if trigger_nestlevel() > 2 return
> A better solution is probably to redesign these tables
> so that there is no need to store the same information
> in two separate places, but for now, using the system
> function trigger_nestlevel() may take care of things.
> Steve Kass
> Drew University
>
> Elisabeth wrote:
>
>
I have the following 2 tables:
tbl_CustomerEmail1
(
CustomerID1 int,
EmailAddress varchar(200),
Unsubscribe bit
)
tbl_CustomerEmail2
(
CustomerID2 int,
EmailAddress varchar(200),
Unsubscribe bit
)
On each table I have a trigger to subscribe/unsubscribe a customer in the
other table if it exists:
CREATE trigger trg_UnsubscribeCustomer1 on tbl_Customer2
for update as
declare @.unsubscribe tinyint
declare @.EMail varchar(200)
select @.unsubscribe = unsubscribe, @.Email = EmailAddress
from inserted
if update (unsubscribe)
begin
if exists (select email from tbl_Customer1 where EmailAddress = @.Email)
begin
update tbl_Customer1
set Unsubscribe = @.Unsubscribe
where EmailAddress = @.Email
return
end
end
and:
CREATE trigger trg_UnsubscribeCustomer2 on tbl_Customer1
for update as
declare @.unsubscribe tinyint
declare @.EMail varchar(200)
select @.unsubscribe = unsubscribe, @.Email = EmailAddress
from inserted
if update (unsubscribe)
begin
if exists (select email from tbl_Customer2 where EmailAddress = @.Email)
begin
update tbl_Customer2
set Unsubscribe = @.Unsubscribe
where EmailAddress = @.Email
return
end
end
These 2 triggers fire each other to the nested limit (32) if I try to
subscribe/unsubscribe a customer which exists in both tables yet I can't
disallow the nested trigger property on the server because it may break othe
r
applications in other DB's. The return statement used doesn't work. Is there
a way I can do this without disabling the nested trigger property on the
whole server?
Many thanks for your help.Elisabeth,
If the updates that fire these triggers are never
called from yet other triggers, you should be able
to handle this by putting the following line of code
at the very beginning of each trigger:
if trigger_nestlevel() > 2 return
A better solution is probably to redesign these tables
so that there is no need to store the same information
in two separate places, but for now, using the system
function trigger_nestlevel() may take care of things.
Steve Kass
Drew University
Elisabeth wrote:
>Hi,
>I have the following 2 tables:
>tbl_CustomerEmail1
>(
>CustomerID1 int,
>EmailAddress varchar(200),
>Unsubscribe bit
> )
>tbl_CustomerEmail2
>(
>CustomerID2 int,
>EmailAddress varchar(200),
>Unsubscribe bit
> )
>On each table I have a trigger to subscribe/unsubscribe a customer in the
>other table if it exists:
>
>CREATE trigger trg_UnsubscribeCustomer1 on tbl_Customer2
>for update as
>
>declare @.unsubscribe tinyint
>declare @.EMail varchar(200)
>select @.unsubscribe = unsubscribe, @.Email = EmailAddress
>from inserted
>if update (unsubscribe)
>begin
> if exists (select email from tbl_Customer1 where EmailAddress = @.Email)
> begin
> update tbl_Customer1
> set Unsubscribe = @.Unsubscribe
> where EmailAddress = @.Email
> return
> end
>end
>and:
>
>CREATE trigger trg_UnsubscribeCustomer2 on tbl_Customer1
>for update as
>
>declare @.unsubscribe tinyint
>declare @.EMail varchar(200)
>select @.unsubscribe = unsubscribe, @.Email = EmailAddress
>from inserted
>if update (unsubscribe)
>begin
> if exists (select email from tbl_Customer2 where EmailAddress = @.Email)
> begin
> update tbl_Customer2
> set Unsubscribe = @.Unsubscribe
> where EmailAddress = @.Email
> return
> end
>end
>
>These 2 triggers fire each other to the nested limit (32) if I try to
>subscribe/unsubscribe a customer which exists in both tables yet I can't
>disallow the nested trigger property on the server because it may break oth
er
>applications in other DB's. The return statement used doesn't work. Is ther
e
>a way I can do this without disabling the nested trigger property on the
>whole server?
>Many thanks for your help.
>
>
>|||worked a treat! cheers mate.
"Steve Kass" wrote:
> Elisabeth,
> If the updates that fire these triggers are never
> called from yet other triggers, you should be able
> to handle this by putting the following line of code
> at the very beginning of each trigger:
> if trigger_nestlevel() > 2 return
> A better solution is probably to redesign these tables
> so that there is no need to store the same information
> in two separate places, but for now, using the system
> function trigger_nestlevel() may take care of things.
> Steve Kass
> Drew University
>
> Elisabeth wrote:
>
>
Subscribe to:
Posts (Atom)