Problem:-
update ServerName.AAD.dbo.t_employeeset ServerName.AAD.dbo.t_employee.work_shift = wa.work_shiftfrom t_employee wawhere ServerName.AAD.dbo.t_employee.id = wa.idand wa.id = '105'
I was getting the following:Server: Msg 117, Level 15, State 2, Line 4The number name 'ServerName.AAD.dbo.t_employee' contains more than the maximum number of prefixes. The maximum is 3.
Solution:-
It's a simple, but not obvious, answer. Put the table you are updating in the FROM clause with an alias and update the alias, as follows:
update laset la.work_shift = wa.work_shiftfrom ServerName.AAD.dbo.t_employee la, t_employee wawhere la.id = wa.idand wa.id = '105'
Monday, May 28, 2007
Sunday, May 27, 2007
Using newsequentialid() in the guid column for sorted values
Okay, so this is a really cool new little feature. A lot of concern with using GUIDs a keys was that they were reasonably random, and they made indexes wildly fragment as they were created. So this little feature allow us to make sure that guids created for a given table are always increasing (so we have less fragmentation, though the indexes can get balanced heavily to one end)
You cannot use them in a query to define them, but you can in a table:
select *from sys.tablesorder by newsequentialid()
Msg 302, Level 16, State 0, Line 1The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.go
You can only use them in a DEFAULT (which you would have known if you hadn't skipped the
create table test( testGuid uniqueidentifier default (newsequentialid()), testId int identity(1,1))goinsert into test default valuesgo 10 --this is a handy little device that was added to 2005 SSMS
Now, we will use the new ranking functions to see that the new GUIDs are in fact ordered:
select testGuid, row_number() over (order by testGuid) as sortByTestGuid, testId, row_number() over (order by testId) as sortByTestIdfrom test
testGuid sortByTestGuid testId sortByTestId------------------------------------ -------------------- ----------- ---------------13C2A79A-B4F5-D911-B7C6-000625ACB4F5 1 1 1030CFDA0-B4F5-D911-B7C6-000625ACB4F5 2 2 2040CFDA0-B4F5-D911-B7C6-000625ACB4F5 3 3 3050CFDA0-B4F5-D911-B7C6-000625ACB4F5 4 4 4060CFDA0-B4F5-D911-B7C6-000625ACB4F5 5 5 5070CFDA0-B4F5-D911-B7C6-000625ACB4F5 6 6 6080CFDA0-B4F5-D911-B7C6-000625ACB4F5 7 7 7090CFDA0-B4F5-D911-B7C6-000625ACB4F5 8 8 80A0CFDA0-B4F5-D911-B7C6-000625ACB4F5 9 9 90B0CFDA0-B4F5-D911-B7C6-000625ACB4F5 10 10 10
Dig those new GUID values. Very interesting. My brain is far too simple to keep up with most of the details of guids, but what really makes me wonder is how they will keep them globally unique AND increasing if they are forcing the domain of guids to always grow. Magic, I will bet, the crafty devils...
One last thing, don't expect the results to look sorted right. Look at the first two rows. 13C2... sorts before 0300... You aren't sorting them by the textual representation, but rather the binary. Run this query and keep inserting data and you will see why:
select testGuid, cast(testGuid as binary(16)), row_number() over (order by testGuid) as sortByTestGuid, row_number() over (order by cast(testGuid as varchar(40))) as sortByTestGuid_Text, testId, row_number() over (order by testId) as sortByTestIdfrom testgo
You cannot use them in a query to define them, but you can in a table:
select *from sys.tablesorder by newsequentialid()
Msg 302, Level 16, State 0, Line 1The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.go
You can only use them in a DEFAULT (which you would have known if you hadn't skipped the
create table test( testGuid uniqueidentifier default (newsequentialid()), testId int identity(1,1))goinsert into test default valuesgo 10 --this is a handy little device that was added to 2005 SSMS
Now, we will use the new ranking functions to see that the new GUIDs are in fact ordered:
select testGuid, row_number() over (order by testGuid) as sortByTestGuid, testId, row_number() over (order by testId) as sortByTestIdfrom test
testGuid sortByTestGuid testId sortByTestId------------------------------------ -------------------- ----------- ---------------13C2A79A-B4F5-D911-B7C6-000625ACB4F5 1 1 1030CFDA0-B4F5-D911-B7C6-000625ACB4F5 2 2 2040CFDA0-B4F5-D911-B7C6-000625ACB4F5 3 3 3050CFDA0-B4F5-D911-B7C6-000625ACB4F5 4 4 4060CFDA0-B4F5-D911-B7C6-000625ACB4F5 5 5 5070CFDA0-B4F5-D911-B7C6-000625ACB4F5 6 6 6080CFDA0-B4F5-D911-B7C6-000625ACB4F5 7 7 7090CFDA0-B4F5-D911-B7C6-000625ACB4F5 8 8 80A0CFDA0-B4F5-D911-B7C6-000625ACB4F5 9 9 90B0CFDA0-B4F5-D911-B7C6-000625ACB4F5 10 10 10
Dig those new GUID values. Very interesting. My brain is far too simple to keep up with most of the details of guids, but what really makes me wonder is how they will keep them globally unique AND increasing if they are forcing the domain of guids to always grow. Magic, I will bet, the crafty devils...
One last thing, don't expect the results to look sorted right. Look at the first two rows. 13C2... sorts before 0300... You aren't sorting them by the textual representation, but rather the binary. Run this query and keep inserting data and you will see why:
select testGuid, cast(testGuid as binary(16)), row_number() over (order by testGuid) as sortByTestGuid, row_number() over (order by cast(testGuid as varchar(40))) as sortByTestGuid_Text, testId, row_number() over (order by testId) as sortByTestIdfrom testgo
Friday, May 25, 2007
Sql server reporting services 2005 - Adding a calender control for Datetime parameter
Just declare the parameter as datetime and the calender control is privileged to show up aside the text box. :-)
This was non-exixtent in the earlier version of SSRS.
This was non-exixtent in the earlier version of SSRS.
Subscribe to:
Posts (Atom)