alternate to "NOT IN " query

any better way of writing this
Select serialnumber from tbl_test2
where serialnumber
not in (select serialnumber from tbl_test) and
exportedflag=0Vikram
Select serialnumber from tbl_test2
where not exists
(select * from tbl_test where tbl_test.serialnumber
=tbl_test2.serialnumber )
and exportedflag=0
"Vikram" <aa@.aa> wrote in message
news:OE88tbrBGHA.3396@.tk2msftngp13.phx.gbl...
> any better way of writing this
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
>|||Also:
Select SerialNumber from tbl_test2
left join tbl_test on tbl_test2.SerialNumber = tbl_test.SerialNumber
where tbl_test.serialNumber is null and exportedFlag=0
"Vikram" <aa@.aa> wrote in message
news:OE88tbrBGHA.3396@.tk2msftngp13.phx.gbl...
> any better way of writing this
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
>

alter view permissions

Hello,
pls help, how can I remove alter view permission for
user who is member of ddladmin db role, select and update
queries shoud remain permited. The user should be able to
edit all other views, stored procedures etc, except this
one view.
thank you for helpHi
Add him to db_datawriter database role but remove him from ddladmin db role
"Gabriel" <anonymous@.discussions.microsoft.com> wrote in message
news:125c01c52f94$9b7e2d10$a501280a@.phx.gbl...
> Hello,
> pls help, how can I remove alter view permission for
> user who is member of ddladmin db role, select and update
> queries shoud remain permited. The user should be able to
> edit all other views, stored procedures etc, except this
> one view.
> thank you for help|||Thanks, I have not mentioned about ability to also create
new views, stored procedures etc. This actions are not
permited with db_datawriter role. Any ideas?

>--Original Message--
>Hi
>Add him to db_datawriter database role but remove him
from ddladmin db role
>
>"Gabriel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:125c01c52f94$9b7e2d10$a501280a@.phx.gbl...
update[vbcol=seagreen]
to[vbcol=seagreen]
this[vbcol=seagreen]
>
>.
>|||One method is to grant CREATE permissions to the user. This will allow the
user to create/alter/drop objects that they own but not objects owned by
other users. You can then change ownership of the view in question to a
different user so that it can't be modified. Other users will need to
owner-qualify object names.
Another approach, which IMHO is better, is to employ a separate database for
those objects you don't want to user to modify. The user can then
db_ddladmin role member in your current database but not in the database
containing the sensitive objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabriel" <anonymous@.discussions.microsoft.com> wrote in message
news:0bbe01c52fa6$ff2468e0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks, I have not mentioned about ability to also create
> new views, stored procedures etc. This actions are not
> permited with db_datawriter role. Any ideas?
>
> from ddladmin db role
> message
> update
> to
> this

alter table set default value for money type column

I run the sql like the following
Alter table ItemStone add ISPurPrice money default 0
then when I select the itemStone table, I find the field ISPurPrice is still
Null, not 0, why?
I'm using SQL Server ver 8.0 (2000)
Thx!!
Kei,
Use the WITH VALUES option in your statement, or (probably better)
declare your new column as NOT NULL. Here are the choices:
Alter table ItemStone add ISPurPrice money NOT NULL default 0
Alter table ItemStone add ISPurPrice money default 0 WITH VALUES
From Books Online, topic ALTER TABLE:
WITH VALUES
Specifies that the value given in DEFAULT constant_expression is stored
in a new column added to existing rows. WITH VALUES can be specified
only when DEFAULT is specified in an ADD column clause. If the added
column allows null values and WITH VALUES is specified, the default
value is stored in the new column added to existing rows. If WITH VALUES
is not specified for columns that allow nulls, the value NULL is stored
in the new column in existing rows. If the new column does not allow
nulls, the default value is stored in new rows regardless of whether
WITH VALUES is specified.
Steve Kass
Drew University
kei wrote:

>I run the sql like the following
>Alter table ItemStone add ISPurPrice money default 0
>then when I select the itemStone table, I find the field ISPurPrice is still
>Null, not 0, why?
>I'm using SQL Server ver 8.0 (2000)
>Thx!!
>

Alter Stored Procedure

Hi all,

I use SQL2005 and I recently noticed this...

When I right click a stored procedure and select modify I get something like this

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xxxxxx]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @.statement = N'

xxx xxx xxx'

instead of the usual alter procedure...

I think that this happened after I installed SP2 (which I cannot remove)

Why this is happening and how can I revert it to the old way of altering stored procs?

In SSMS click Tools > Options then Scripting on the left. "Object Scripting options" section set "Include IF NOT EXISTS clause" to false.

Alter Column to IDENTITY

I used Select Into... to duplicate a table, but lost the IDENTITY and PK
constraints in the process. How do I get those back? Thanks.
Here are the details:
Original Table:
TABLE A (
[ID] INT IDENTITY PRIMARY KEY
, ColA smallint NOT NULL
, ColB varchar(20) NOT NULL
, ColC int NULL
, ColD numeric(38, 6) NULL
)
After SELECT * INTO TABLE_B FROM TABLE_A:
TABLE B (
[ID] INT NOT NULL
, ColA smallint NOT NULL
, ColB varchar(20) NOT NULL
, ColC int NULL
, ColD numeric(38, 6) NULL
)
I tried something like this, but it didn't work:
ALTER TABLE TABLE_B
ALTER COLUMN [ID] INT IDENTITY PRIMARY KEY> I tried something like this, but it didn't work:
> ALTER TABLE TABLE_B
> ALTER COLUMN [ID] INT IDENTITY PRIMARY KEY
You can't do this; T-SQL does not allow you to add or drop the IDENTITY
property. What you need to do can be better illustrated by performing the
action in Enterprise Mangler and viewing the code that it produces. It's
rather ugly, basically it creates a work table, moves all the data over,
drops the original and renames the new. All with a huge xlock, of course.
I shouldn't have to tell you what this means on a large-ish table.|||Short answer - you can't. But you can add a *new* identity column to the
table then drop the old column. Easiest way might be to make the change via
EM - design table and save the change script.
HTH
Jerry
"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:6E0241AC-792C-4D1A-92E4-9CFCF99413AF@.microsoft.com...
>I used Select Into... to duplicate a table, but lost the IDENTITY and PK
> constraints in the process. How do I get those back? Thanks.
> Here are the details:
> Original Table:
> TABLE A (
> [ID] INT IDENTITY PRIMARY KEY
> , ColA smallint NOT NULL
> , ColB varchar(20) NOT NULL
> , ColC int NULL
> , ColD numeric(38, 6) NULL
> )
> After SELECT * INTO TABLE_B FROM TABLE_A:
> TABLE B (
> [ID] INT NOT NULL
> , ColA smallint NOT NULL
> , ColB varchar(20) NOT NULL
> , ColC int NULL
> , ColD numeric(38, 6) NULL
> )
> I tried something like this, but it didn't work:
> ALTER TABLE TABLE_B
> ALTER COLUMN [ID] INT IDENTITY PRIMARY KEY|||Drop the column and add it again as an Identity column
http://sqlservercode.blogspot.com/
"Boddhicitta" wrote:

> I used Select Into... to duplicate a table, but lost the IDENTITY and PK
> constraints in the process. How do I get those back? Thanks.
> Here are the details:
> Original Table:
> TABLE A (
> [ID] INT IDENTITY PRIMARY KEY
> , ColA smallint NOT NULL
> , ColB varchar(20) NOT NULL
> , ColC int NULL
> , ColD numeric(38, 6) NULL
> )
> After SELECT * INTO TABLE_B FROM TABLE_A:
> TABLE B (
> [ID] INT NOT NULL
> , ColA smallint NOT NULL
> , ColB varchar(20) NOT NULL
> , ColC int NULL
> , ColD numeric(38, 6) NULL
> )
> I tried something like this, but it didn't work:
> ALTER TABLE TABLE_B
> ALTER COLUMN [ID] INT IDENTITY PRIMARY KEY|||Thanks everyone. These are all kind of yucky for different reasons. Is
there a better way to copy a table and retain the constraints? DTS?
"Boddhicitta" wrote:

> I used Select Into... to duplicate a table, but lost the IDENTITY and PK
> constraints in the process. How do I get those back? Thanks.
> Here are the details:
> Original Table:
> TABLE A (
> [ID] INT IDENTITY PRIMARY KEY
> , ColA smallint NOT NULL
> , ColB varchar(20) NOT NULL
> , ColC int NULL
> , ColD numeric(38, 6) NULL
> )
> After SELECT * INTO TABLE_B FROM TABLE_A:
> TABLE B (
> [ID] INT NOT NULL
> , ColA smallint NOT NULL
> , ColB varchar(20) NOT NULL
> , ColC int NULL
> , ColD numeric(38, 6) NULL
> )
> I tried something like this, but it didn't work:
> ALTER TABLE TABLE_B
> ALTER COLUMN [ID] INT IDENTITY PRIMARY KEY|||Sure, create the table with the constraints in place, then insert the data
(specifying all columns except the identity column).
A
"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:40361790-544E-4404-91B0-8F88D1F97B97@.microsoft.com...
> Thanks everyone. These are all kind of yucky for different reasons. Is
> there a better way to copy a table and retain the constraints? DTS?|||Try this:
CREATE TABLE B (
[ID] INT IDENTITY PRIMARY KEY
, ColA smallint NOT NULL
, ColB varchar(20) NOT NULL
, ColC int NULL
, ColD numeric(38, 6) NULL
)
GO
SET IDENTITY_INSERT B ON
GO
INSERT INTO B ([ID], ColA, ColB, ColC, ColD)
SELECT [ID], ColA, ColB, ColC, ColD FROM A
SET IDENTITY_INSERT B OFF
GO
"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:40361790-544E-4404-91B0-8F88D1F97B97@.microsoft.com...
> Thanks everyone. These are all kind of yucky for different reasons. Is
> there a better way to copy a table and retain the constraints? DTS?
> "Boddhicitta" wrote:
>|||> Enterprise Mangler
LOL!! Thanks, I really needed that! HA HA HA HA!
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Oops, typo. :-)
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23j9uk1O1FHA.3376@.TK2MSFTNGP14.phx.gbl...
> LOL!! Thanks, I really needed that! HA HA HA HA!|||While I grateful for the DDL, I hope that you are not you actaully
using IDENTITY as a key? FUNDAMENTA! WRONG ! Look for a relational
key.
Surely you had enough basic to know that code like "IDENTITY PRIMARY
KEY " means thart you have ABSOLUTELY NO CONCEPT OF RbMS.

ALTER AND UPDATE together not working....

Hi ,
I have a Stored Procedure like this...
| CREATE PROCEDURE V_test AS
| Select MS.memno, MS.ymdeff, MS.ymdend,MS.Aidcode INTO
STAGE_memspan
| FROM membspan MS INNER JOIN STAGE_members MM
I | on SM.memno = MM.memno
|
| ALTER TABLE STAGE_membspan ADD Aidcode_Description char (72)
NULL
| UPDATE STAGE_membspan
| SET Aidcode_Description = CL.[desc]
II | from SATGE_membspan SM, CODE_LOOKUP CL
| where SM.Aidcode = CL.code and CL.id = 'rp'
After executing this I am getting following error :
Invalid Column name 'Aidcode_Description'
If I execute I part seperately and II part seperately it work fine...
Thanks...!!!!.When the parser is examiningthe statement and validating all the =columnames etc tye ALTER has not yet been done, therefore when it =examines the update the column being referenced does not exist. Run =these as two separate batches and all will be fine. Not really sure why =you want an alter in a stored proc anyway since by definition it can =only be done once, and the major benefit of stored procs comes when they =are run many times.
Mike John
"veena" <vgs@.yahoo.com> wrote in message =news:uMfXdWITDHA.1576@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> > I have a Stored Procedure like this...
> > | CREATE PROCEDURE V_test AS
> | Select MS.memno, MS.ymdeff, MS.ymdend,MS.Aidcode INTO
> STAGE_memspan
> | FROM membspan MS INNER JOIN STAGE_members MM
> I | on SM.memno =3D MM.memno
> |
> | ALTER TABLE STAGE_membspan ADD Aidcode_Description char =(72)
> NULL
> > | UPDATE STAGE_membspan
> | SET Aidcode_Description =3D CL.[desc]
> II | from SATGE_membspan SM, CODE_LOOKUP CL
> | where SM.Aidcode =3D CL.code and CL.id =3D 'rp'
> > > After executing this I am getting following error :
> Invalid Column name 'Aidcode_Description'
> > If I execute I part seperately and II part seperately it work fine...
> > Thanks...!!!!.
> > > >=20|||Thanks Mike,
I came to know the reason and also I am not including ALTER in a stored
procedure...since its a one time execution process... Thanks again...
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:#2zPClITDHA.1912@.tk2msftngp13.phx.gbl...
When the parser is examiningthe statement and validating all the columnames
etc tye ALTER has not yet been done, therefore when it examines the update
the column being referenced does not exist. Run these as two separate
batches and all will be fine. Not really sure why you want an alter in a
stored proc anyway since by definition it can only be done once, and the
major benefit of stored procs comes when they are run many times.
Mike John
"veena" <vgs@.yahoo.com> wrote in message
news:uMfXdWITDHA.1576@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> I have a Stored Procedure like this...
> | CREATE PROCEDURE V_test AS
> | Select MS.memno, MS.ymdeff, MS.ymdend,MS.Aidcode INTO
> STAGE_memspan
> | FROM membspan MS INNER JOIN STAGE_members MM
> I | on SM.memno = MM.memno
> |
> | ALTER TABLE STAGE_membspan ADD Aidcode_Description char (72)
> NULL
> | UPDATE STAGE_membspan
> | SET Aidcode_Description = CL.[desc]
> II | from SATGE_membspan SM, CODE_LOOKUP CL
> | where SM.Aidcode = CL.code and CL.id = 'rp'
>
> After executing this I am getting following error :
> Invalid Column name 'Aidcode_Description'
> If I execute I part seperately and II part seperately it work fine...
> Thanks...!!!!.
>
>

Also cannot display other windows to.

I use sql server 2005 developer edition with service pack 1.

When i right click on a database and i select properties an error occured with the folowing stack trace

===================================

Cannot show requested dialog.

===================================

Cannot show requested dialog. (SqlMgmt)

Program Location:

at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.AllocateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider, CDataContainer dc)
at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.Microsoft.SqlServer.Management.SqlMgmt.ILaunchFormHostedControlAllocator.CreateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)
at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()

===================================

Object reference not set to an instance of an object. (System.Data)

Program Location:

at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ReadColumnData()
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.DatabaseLevel.GetData(EnumResult res)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetObjectByKey(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.DatabaseCollection.get_Item(String name)
at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.DatabaseData..ctor(CDataContainer context, String databaseName)
at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.LoadDefinition(String newName)
at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype..ctor(CDataContainer context)
at Microsoft.SqlServer.Management.SqlManagerUI.DBPropSheet..ctor(CDataContainer context)

Accroding to the reflected sources:

public DatabaseData(CDataContainer context, string databaseName)
{
this.mirrorSafetyLevel = MirroringSafetyLevel.Off;
this.witnessServer = string.Empty;
Database database1 = context.Server.Databases[databaseName];

There might be a problem in getting the information from the database collection. So do the following steps:

-Run the profiler to the when the execution of the command stops. (Guess it has to do something with the database name)
-Select the database name from the sysdatabases and post it here

SELECT Name, DATALENGTH(Name),LEN(Name) from sys.databases

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

When i try to create a new view, in any of the databases i saw an error

Object reference not set to an instance of an object. (SQLEditors)

Program Location:

at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ReadColumnData()
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.DatabaseLevel.GetData(EnumResult res)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname)
at Microsoft.SqlServer.Management.Smo.Database.get_DefaultSchema()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.GetDefaultSchema(Server server, String databaseName)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.GenerateNewObjectUrn()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.SetObjectAndParentUrns(Urn originalUrn)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProjectNode..ctor(Urn urn, DocumentOptions options, IManagedConnection connection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProjectNode.Allocate(Urn origUrn, DocumentType editorType, DocumentOptions options, IManagedConnection connection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

I think that there is a problem with the server installation. I will try to reinstalle it.

|||If you want to solve the problem, follow the mentioned steps to reproduce the executed script on the server. That might also help others to solve their problems and help to improve the product itself.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Please make sure the database exists and is not deleted by clicking refresh on the server node and see if you can still see the database whose properties you were not able to access. I believe the database was dropped by some other means and your SSMS window was not refreshed after that.

Hope this helps.

Thanks,

Sravanthi.

|||Stack trace dump means there might be a probelm with the Windows, a virus or mismatch of hotfix/service pack on operating system. Make sure to check what has been changed since this was working correctly in previous state, if not you might try testing the same on other machine.|||All databases are in place. i can open tables and see their data.|||

thats what i believe too.

The problem appears after an update from microsoft windows update which find that my windows sql server installation need the service pack 1 update. I selected and install it.

after that i download and install service pack 2 for sql server 2005 but of course this doesn't correct anything.

I have in the same computer a sqlserver express edition installed and from microsoft sql server managment studio i can work properly with this instance without a problem (in case i thought that was a problem from microsoft sql server managment studio).

About response from Jens K. Suessmeyer .

Whene i execute the line

SELECT 'AdventureWorks', DATALENGTH('AdventureWorks'),LEN('AdventureWorks') from sys.databases

i get

'An error occurred while executing batch. Error message is: Object reference not set to an instance of an object.'

with any database there are in this installation.

|||

Hi,

From where are you running the queries? Please run the query SELECT Name, DATALENGTH(Name),LEN(Name) from sys.databases (run it as is as Jens K. Suessmeyer has given, dont replace Name in the query, if you want it specific to AdventureWorks just add a where clause). Run this query from new query window in SSMS and let us know the output. From the error that you are getting "Object reference..." looks like you are trying to run the query programmatically. Just run it from a SSMS query window and let us know the output. Make sure the database that is causing all these issues comes up in the query result.

Sravanthi

|||If you believe its SSMS tools problem, try to reinstall them again.|||

All databases causing that issues

The result after executing the above query is

NAME (no column name) (no column name)

master 12 6
tempdb 12 6
model 10 5
msdb 8 4
ReportServer$MAIMOY2005 46 23
ReportServer$MAIMOY2005TempDB 58 29
BASE DE DATOS ORIGINAL 44 22
ALKI 8 4
aspnetdb_ALKI 26 13
AdventureWorks 28 14
AdventureWorksDW 32 16

(11 row(s) affected)

|||After a full uninstall and reinstall everything seems to work perfect. Aftes installation i install also service pack 2 downloaded and installed locally and everything works properly.|||

somehow, i missed this thread and I am sure if I could furnish this information bit earlier it would have been helpful. nevertheless, i think i should share my experience in this regards. the story is as follows :)

One of our development server had the same problem and I have documented this error. But at that time I was on the tows and somehow I was to get rid of this problem and I did the same trick - reinstalling the SQL Server. But I was not satisfied by this solution. when I did the postmortem of the process then I realized that our TL used to synchronies the Development database from Visio. There were many connection used to connect to different database (from visio) and one of them was to connect to master database. He used the master connection , and Visio automatically detects the objects in the connected database which are not there in the Model and it ask whether u want to delete those object or not. He selected Yes and Visio deleted all the objects from master database which are not there in the model. I verified the objects between two instances Master databases. There were five system tables missing , the missing tables were spt_fallback_db,spt_fallback_dev,spt_fallback_usg,spt_monitor,spt_value. Then I created the script of these tables from other instance and run on the problem server, but those tables were not having owner , though it shows owner as DBO. Actually these tables comes under System Tables tree but when I created these by the script those created as user table. I was pretty sure that these problem were because of these tables got deleted. But I was not having time to do R&D on this and I reinstalled the instance.

(a) How come Visio able to delete system tables (the irony is that , in SSMO these tables are shown as System Tables , but if u use sp_help it is shown as Usertable).

(b) IF somehow these tables got deleted, how can we restore these table and revert back to normal stage without reinstalling anything.

Also question to Antonisk, is something like this was happened in your side…

I think we need to dig out the root of this problem. If these tables are so critical , then these should not be deletable from anywhere. If it is a bug the we need to report this to MS..

Thanks for the time

Madhu

|||I don't use Visio at all. Also i can't check if this was the problem (system tables missing) because I reinstall the Sql Server.

Alpha-Numeric Collation

I have a column of data that needs to be sorted alpha-numerically.
Rules: I can not do this in a SQL Select statement or programming on the
middle-tier. It must be set as an index or some sort of collation property
of the collumn.
Data Example: This is the order it is returning.
PSW1
PSW2
PSW17
PSW3
I need it to return like this:
PSW1
PSW2
PSW3
PSW17
Is this possible with a certain type of index or collation setting?
Firstly this is not having a go at you personally but this question occurs
time and time again so I will explain my experiences and solution.
Secondly it does not provide you with a complete solution. I'm sorry. (but
you may be able to write a function that returns an integer of the first
numeric and then sort on the substrings)
ORDER BY SUBSTRING([Key], 1, [dbo].fn_FirstNumeric([Key]) -1),
CONVERT(INT,SUBSTRING([Key], [dbo].fn_FirstNumeric([Key]), 999))
WHERE the function looks like
CREATE FUNCTION fn_FirstNumeric ( @.Data NVARCHAR(255) ) RETURNS INT
AS
BEGIN
RETURN PATINDEX('%[0-9]%', @.Data)
END
It may be that my explanation of your situation is not correct but it seems
that you have a key PSW17 which should be further sub-divided into
constituent keys.
While working for a very large semi-conductor manufacturing company I was
asked to perform exactly the same sort of collation/sorting on part numbers
such as
SNJ74LS145J
SNC72LP245N
SNB74S174N
the first 3 characters meant something specific so did the next 2 numbers,
the middle letters also, then the next digits something else and finally the
last character.
If I remember correctly it was (in that order)
Specification type/burn type (components were subjected to high temperaturs
and re-tested, those that still worked were sold to military)
Can't remember the next one
Another spec type L = low power, LS = low power Schottkey
Parent bar type (all 174 bars became either military, standard, encapsulated
in plastic or ceramic).
Package (plastic, ceramic)
You can imagine trying to get at data when asked
"I want all low power schottkey with parent bar 174 for non-military"
So we decided that at the point of data entry the part number would be split
up into into constituent parts and for display puproses the parts were
concatenated to provide the complete part number. We could then process the
data as requested as each part was in a seperate column.
So what's is the point of explaining this?
Well, SQL manages "Databases" the first part of this word is Data and if
this is not correct then you do not have a database but a base of data which
your will struggle with. You must correctly normalize and identify all
correct data parts, keys and sub-key for your system to work correctly.
Nik Marshall-Blank MCSD/MCDBA
"Eric Laechelin" <EricLaechelin@.discussions.microsoft.com> wrote in message
news:36414A3C-54F4-43FD-9633-F95918E47A84@.microsoft.com...
>I have a column of data that needs to be sorted alpha-numerically.
> Rules: I can not do this in a SQL Select statement or programming on the
> middle-tier. It must be set as an index or some sort of collation
> property
> of the collumn.
> Data Example: This is the order it is returning.
> PSW1
> PSW2
> PSW17
> PSW3
> I need it to return like this:
> PSW1
> PSW2
> PSW3
> PSW17
> Is this possible with a certain type of index or collation setting?

Alpha-Numeric Collation

I have a column of data that needs to be sorted alpha-numerically.
Rules: I can not do this in a SQL Select statement or programming on the
middle-tier. It must be set as an index or some sort of collation property
of the collumn.
Data Example: This is the order it is returning.
PSW1
PSW2
PSW17
PSW3
I need it to return like this:
PSW1
PSW2
PSW3
PSW17
Is this possible with a certain type of index or collation setting?Firstly this is not having a go at you personally but this question occurs
time and time again so I will explain my experiences and solution.
Secondly it does not provide you with a complete solution. I'm sorry. (but
you may be able to write a function that returns an integer of the first
numeric and then sort on the substrings)
ORDER BY SUBSTRING([Key], 1, [dbo].fn_FirstNumeric([Key]) -1),
CONVERT(INT,SUBSTRING([Key], [dbo].fn_FirstNumeric([Key]), 999))
WHERE the function looks like
CREATE FUNCTION fn_FirstNumeric ( @.Data NVARCHAR(255) ) RETURNS INT
AS
BEGIN
RETURN PATINDEX('%[0-9]%', @.Data)
END
It may be that my explanation of your situation is not correct but it seems
that you have a key PSW17 which should be further sub-divided into
constituent keys.
While working for a very large semi-conductor manufacturing company I was
asked to perform exactly the same sort of collation/sorting on part numbers
such as
SNJ74LS145J
SNC72LP245N
SNB74S174N
the first 3 characters meant something specific so did the next 2 numbers,
the middle letters also, then the next digits something else and finally the
last character.
If I remember correctly it was (in that order)
Specification type/burn type (components were subjected to high temperaturs
and re-tested, those that still worked were sold to military)
Can't remember the next one
Another spec type L = low power, LS = low power Schottkey
Parent bar type (all 174 bars became either military, standard, encapsulated
in plastic or ceramic).
Package (plastic, ceramic)
You can imagine trying to get at data when asked
"I want all low power schottkey with parent bar 174 for non-military"
So we decided that at the point of data entry the part number would be split
up into into constituent parts and for display puproses the parts were
concatenated to provide the complete part number. We could then process the
data as requested as each part was in a seperate column.
So what's is the point of explaining this?
Well, SQL manages "Databases" the first part of this word is Data and if
this is not correct then you do not have a database but a base of data which
your will struggle with. You must correctly normalize and identify all
correct data parts, keys and sub-key for your system to work correctly.
Nik Marshall-Blank MCSD/MCDBA
"Eric Laechelin" <EricLaechelin@.discussions.microsoft.com> wrote in message
news:36414A3C-54F4-43FD-9633-F95918E47A84@.microsoft.com...
>I have a column of data that needs to be sorted alpha-numerically.
> Rules: I can not do this in a SQL Select statement or programming on the
> middle-tier. It must be set as an index or some sort of collation
> property
> of the collumn.
> Data Example: This is the order it is returning.
> PSW1
> PSW2
> PSW17
> PSW3
> I need it to return like this:
> PSW1
> PSW2
> PSW3
> PSW17
> Is this possible with a certain type of index or collation setting?

Alpha-Numeric Collation

I have a column of data that needs to be sorted alpha-numerically.
Rules: I can not do this in a SQL Select statement or programming on the
middle-tier. It must be set as an index or some sort of collation property
of the collumn.
Data Example: This is the order it is returning.
PSW1
PSW2
PSW17
PSW3
I need it to return like this:
PSW1
PSW2
PSW3
PSW17
Is this possible with a certain type of index or collation setting?Firstly this is not having a go at you personally but this question occurs
time and time again so I will explain my experiences and solution.
Secondly it does not provide you with a complete solution. I'm sorry. (but
you may be able to write a function that returns an integer of the first
numeric and then sort on the substrings)
ORDER BY SUBSTRING([Key], 1, [dbo].fn_FirstNumeric([Key]) -1),
CONVERT(INT,SUBSTRING([Key], [dbo].fn_FirstNumeric([Key]), 999))
WHERE the function looks like
CREATE FUNCTION fn_FirstNumeric ( @.Data NVARCHAR(255) ) RETURNS INT
AS
BEGIN
RETURN PATINDEX('%[0-9]%', @.Data)
END
It may be that my explanation of your situation is not correct but it seems
that you have a key PSW17 which should be further sub-divided into
constituent keys.
While working for a very large semi-conductor manufacturing company I was
asked to perform exactly the same sort of collation/sorting on part numbers
such as
SNJ74LS145J
SNC72LP245N
SNB74S174N
the first 3 characters meant something specific so did the next 2 numbers,
the middle letters also, then the next digits something else and finally the
last character.
If I remember correctly it was (in that order)
Specification type/burn type (components were subjected to high temperaturs
and re-tested, those that still worked were sold to military)
Can't remember the next one
Another spec type L = low power, LS = low power Schottkey
Parent bar type (all 174 bars became either military, standard, encapsulated
in plastic or ceramic).
Package (plastic, ceramic)
You can imagine trying to get at data when asked
"I want all low power schottkey with parent bar 174 for non-military"
So we decided that at the point of data entry the part number would be split
up into into constituent parts and for display puproses the parts were
concatenated to provide the complete part number. We could then process the
data as requested as each part was in a seperate column.
So what's is the point of explaining this?
Well, SQL manages "Databases" the first part of this word is Data and if
this is not correct then you do not have a database but a base of data which
your will struggle with. You must correctly normalize and identify all
correct data parts, keys and sub-key for your system to work correctly.
--
Nik Marshall-Blank MCSD/MCDBA
"Eric Laechelin" <EricLaechelin@.discussions.microsoft.com> wrote in message
news:36414A3C-54F4-43FD-9633-F95918E47A84@.microsoft.com...
>I have a column of data that needs to be sorted alpha-numerically.
> Rules: I can not do this in a SQL Select statement or programming on the
> middle-tier. It must be set as an index or some sort of collation
> property
> of the collumn.
> Data Example: This is the order it is returning.
> PSW1
> PSW2
> PSW17
> PSW3
> I need it to return like this:
> PSW1
> PSW2
> PSW3
> PSW17
> Is this possible with a certain type of index or collation setting?

Almost there (I think)...SQL Update problem...

I am trying to update a single field in a SQL database table. I created a SQLDataSource, configured Select and Update queries, and wrote some code in the script block to do the update after a button click. The SQLDataSource is in a contentplaceholder. What am I doing wrong in the data source, the script block, or both? Thanks so much in advance...

Here is the code for the SQLDataSource:

Dim ImageUploaded As Integer = 2

srcUpdateImageUploaded.UpdateParameters("@.ImageUploaded").DefaultValue = ImageUploaded

srcUpdateImageUploaded.Update()

Here is the code in the script block:

<asp:SqlDataSource ID="srcUpdateImageUploaded" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT [ImageUploaded] FROM [profiles_BasicProperties] WHERE ([UserName] = @.UserName)"
UpdateCommand="UPDATE profiles_BasicProperties SET [ImageUploaded] = ?">
<UpdateParameters>
<asp:ControlParameter ControlID="TextBox1" Name="EmilyTheKitty" PropertyName="Text" Type="Object" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="UserName" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

Here is the error that I get:

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 164: Dim ImageUploaded As Integer = 2
Line 165:
Line 166: srcUpdateImageUploaded.UpdateParameters("@.ImageUploaded").DefaultValue = ImageUploaded
Line 167: srcUpdateImageUploaded.Update()
Line 168:

Source File: C:\Users\Matthew\Documents\Group 02 - Politicore\PC_Dev\Profiles_BuildProfile.aspx Line: 166

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
ASP.profiles_buildprofile_aspx.PictureUpload(Object sender, EventArgs e) in C:\Users\Matthew\Documents\Group 02 - Politicore\PC_Dev\Profiles_BuildProfile.aspx:166
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +104
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5614

There is no @.ImageUploaded parameter in your SQLDataSource, See the modified code below

<asp:SqlDataSource ID="srcUpdateImageUploaded" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT [ImageUploaded] FROM [profiles_BasicProperties] WHERE ([UserName] = @.UserName)"
UpdateCommand="UPDATE profiles_BasicProperties SET [ImageUploaded] = @.ImageUploaded">
<UpdateParameters>
<asp:ControlParameter ControlID="TextBox1" Name="ImageUploaded" PropertyName="Text" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="UserName" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

|||

I appreciate the response...but it didn't work. I tried the modified code you posted. I pasted it into my page, tried it, and got the "Object reference not set to an instance of an object" again. Here is the code that I copied out of my page (it's the same code you posted)...

<asp:SqlDataSourceID="srcUpdateImageUploaded"runat="server"ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"

ProviderName="System.Data.SqlClient"

SelectCommand="SELECT [ImageUploaded] FROM [profiles_BasicProperties] WHERE ([UserName] = @.UserName)"

UpdateCommand="UPDATE profiles_BasicProperties SET [ImageUploaded] = @.ImageUploaded">

<UpdateParameters>

<asp:ControlParameterControlID="TextBox1"Name="ImageUploaded"PropertyName="Text"Type="Int32"/>

</UpdateParameters>

<SelectParameters>

<asp:ControlParameterControlID="TextBox1"Name="UserName"PropertyName="Text"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

|||

where you are trying to update your code?? is it in the page load event ?? and also try to change asp:controlParameter into asp:FormParameter. If still not solved pls paste the whole code I will look into it

|||

Okay...I changed my mind on how I want to do this. I did away with the SQL data source connection object and I want to do this entirely with code in the script block. There is a command button that is clicked which invokes the following code. A textbox is included on the page and it is called by the code. The new error I get is this:

" Error updating table. Must declare the scalar variable "@.updatevalue". "

Here is the entirety of the code that is called:

ProtectedSub cmdUpdate_Click(ByVal senderAsObject, _

ByVal eAs EventArgs)Handles cmdUpdate.Click

'a temporary variable that is hard coded to 2 for testing...

Dim updatevalueAs Int32

updatevalue = 2

Dim usernameAsString

username = txtUserName.Text

Dim connectionstringAsString

connectionstring ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"

' Define ADO.NET objects.

Dim updateSQLAsString

updateSQL ="UPDATE profiles_BasicProperties SET "

updateSQL &="ImageUploaded=@.updatevalue "

updateSQL &="WHERE username=@.username"

Dim conAsNew SqlConnection(connectionString)

Dim cmdAsNew SqlCommand(updateSQL, con)

' Add the parameters.

cmd.Parameters.AddWithValue("@.ImageUploaded", updatevalue)

' Try to open database and execute the update.

Try

con.Open()

Dim updatedAsInteger = cmd.ExecuteNonQuery()

lblResults.Text = updated.ToString() &" records updated."

Catch errAs Exception

lblresults.Text ="Error updating table. "

lblResults.Text &= err.Message

Finally

con.Close()

EndTry

EndSub

|||

Okay, I fixed my own problem. I also figured out how these lines are put together so I am beyond merely cutting and pasting code in from books. Here is the correct code (corrected lines in bold, italics, and underlined):

ProtectedSub cmdUpdate_Click(ByVal senderAsObject, _

ByVal eAs EventArgs)Handles cmdUpdate.Click

'a temporary variable that is hard coded to 2 for testing...

Dim updatevalueAs Int32

updatevalue = 2

Dim usernameAsString

username = txtUserName.Text

Dim connectionstringAsString

connectionstring ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"

' Define ADO.NET objects.

Dim updateSQLAsString

updateSQL ="UPDATE profiles_BasicProperties SET "

updateSQL &="ImageUploaded=@.ImageUploaded "

updateSQL &="WHERE username=@.username"

Dim conAsNew SqlConnection(connectionString)

Dim cmdAsNew SqlCommand(updateSQL, con)

' Add the parameters.

cmd.Parameters.AddWithValue("@.ImageUploaded", updatevalue)

cmd.Parameters.AddWithValue("@.username", username)

' Try to open database and execute the update.

Try

con.Open()

Dim updatedAsInteger = cmd.ExecuteNonQuery()

lblResults.Text = updated.ToString() &" records updated."

Catch errAs Exception

lblresults.Text ="Error updating table. "

lblResults.Text &= err.Message

Finally

con.Close()

EndTry

EndSub

Allowing parameters to be used inside IN cluase

Are there any plans to allow this in future versions of sql server?
e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
This is currently allowed. What problem are you having?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
Are there any plans to allow this in future versions of sql server?
e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
|||It's already there:
create table #tempin(anint int)
go
insert #tempin values(1)
insert #tempin values(2)
go
declare @.int1 int
declare @.int2 int
set @.int1 = 1
set @.int2 = 2
select * from #tempin
where anint in (@.int1, @.int2)
go
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> Are there any plans to allow this in future versions of sql server?
> e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
>
|||My bad, misunderstood what my sql admin told me.
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> Are there any plans to allow this in future versions of sql server?
> e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
>
|||He probably meant:
DECLARE @.ids VARCHAR(255)
SET @.ids = '1, 2, 4, 5, 6'
SELECT id FROM table WHERE id IN (@.ids)
See http://www.aspfaq.com/2248
http://www.aspfaq.com/
(Reverse address to reply.)
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:uOoo6bFaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> My bad, misunderstood what my sql admin told me.
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
wrote
> in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
>

Allowing parameters to be used inside IN cluase

Are there any plans to allow this in future versions of sql server?
e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)This is currently allowed. What problem are you having?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
Are there any plans to allow this in future versions of sql server?
e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)|||It's already there:
create table #tempin(anint int)
go
insert #tempin values(1)
insert #tempin values(2)
go
declare @.int1 int
declare @.int2 int
set @.int1 = 1
set @.int2 = 2
select * from #tempin
where anint in (@.int1, @.int2)
go
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> Are there any plans to allow this in future versions of sql server?
> e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
>|||My bad, misunderstood what my sql admin told me.
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> Are there any plans to allow this in future versions of sql server?
> e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
>|||He probably meant:
DECLARE @.ids VARCHAR(255)
SET @.ids = '1, 2, 4, 5, 6'
SELECT id FROM table WHERE id IN (@.ids)
See http://www.aspfaq.com/2248
http://www.aspfaq.com/
(Reverse address to reply.)
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:uOoo6bFaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> My bad, misunderstood what my sql admin told me.
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
wrote
> in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
>

Allowing parameters to be used inside IN cluase

Are there any plans to allow this in future versions of sql server?
e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)This is currently allowed. What problem are you having?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
Are there any plans to allow this in future versions of sql server?
e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)|||It's already there:
create table #tempin(anint int)
go
insert #tempin values(1)
insert #tempin values(2)
go
declare @.int1 int
declare @.int2 int
set @.int1 = 1
set @.int2 = 2
select * from #tempin
where anint in (@.int1, @.int2)
go
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> Are there any plans to allow this in future versions of sql server?
> e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
>|||My bad, misunderstood what my sql admin told me.
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> Are there any plans to allow this in future versions of sql server?
> e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
>|||He probably meant:
DECLARE @.ids VARCHAR(255)
SET @.ids = '1, 2, 4, 5, 6'
SELECT id FROM table WHERE id IN (@.ids)
See http://www.aspfaq.com/2248
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:uOoo6bFaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> My bad, misunderstood what my sql admin told me.
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
wrote
> in message news:OPBw7IFaEHA.3244@.TK2MSFTNGP12.phx.gbl...
> > Are there any plans to allow this in future versions of sql server?
> >
> > e.x.: SELECT * FROM X WHERE Y IN(@.A, @.B, @.C)
> >
> >
>

Allow users to select their own sorting

Here is my question.
Sorting and grouping question by allowing users to select the sorting field
I have a report where I am giving the users a parameter so that they can
select which field they would like to sort on.The report is also grouping by
that field. I have a gruping section, where i have added code to group on the
field I want based on this parameter, however I also would like to change the
sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and
group by SalesPerson or Customer. Right now, I have code on my dataset to
sort by SalesPerson Code and Order No. So far the grouping works, however the
sorting does not.
So when the users selects SalesPerson from the report parameter, it will
sort by salesPerson and group by each salesperson, and when they select
Customer, it should group and sort by customer. The grouping section already
works, but I can not figure out where to change the sorting rule.
Any suggestions would help.which version of reporting services are you using?
if you are using 2005 then sorting is built in to the front end
see 'interactive sorting' at
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/7eb8dcef-d152-470d-b440-3aaa2c26fb56.htm
"armela" wrote:
> Here is my question.
> Sorting and grouping question by allowing users to select the sorting field
>
> I have a report where I am giving the users a parameter so that they can
> select which field they would like to sort on.The report is also grouping by
> that field. I have a gruping section, where i have added code to group on the
> field I want based on this parameter, however I also would like to change the
> sorting order but I checked around and I did not find any info.
> So here is my example. I am showing sales order info.The user can sort and
> group by SalesPerson or Customer. Right now, I have code on my dataset to
> sort by SalesPerson Code and Order No. So far the grouping works, however the
> sorting does not.
> So when the users selects SalesPerson from the report parameter, it will
> sort by salesPerson and group by each salesperson, and when they select
> Customer, it should group and sort by customer. The grouping section already
> works, but I can not figure out where to change the sorting rule.
>
> Any suggestions would help.
>
>
>
>|||Hi there.
I am using SQL 2005 SP1. I tried to access the website you referred to, but
that did not take me anywhere.
Can you give me website address again '
Thanks
"adolf garlic" wrote:
> which version of reporting services are you using?
> if you are using 2005 then sorting is built in to the front end
> see 'interactive sorting' at
> ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/7eb8dcef-d152-470d-b440-3aaa2c26fb56.htm
>
> "armela" wrote:
> > Here is my question.
> >
> > Sorting and grouping question by allowing users to select the sorting field
> >
> >
> >
> > I have a report where I am giving the users a parameter so that they can
> > select which field they would like to sort on.The report is also grouping by
> > that field. I have a gruping section, where i have added code to group on the
> > field I want based on this parameter, however I also would like to change the
> > sorting order but I checked around and I did not find any info.
> >
> > So here is my example. I am showing sales order info.The user can sort and
> > group by SalesPerson or Customer. Right now, I have code on my dataset to
> > sort by SalesPerson Code and Order No. So far the grouping works, however the
> > sorting does not.
> > So when the users selects SalesPerson from the report parameter, it will
> > sort by salesPerson and group by each salesperson, and when they select
> > Customer, it should group and sort by customer. The grouping section already
> > works, but I can not figure out where to change the sorting rule.
> >
> >
> >
> > Any suggestions would help.
> >
> >
> >
> >
> >
> >
> >|||Maybe you misunderstood my question
I have a paramenter on the option's screen called sorting option and the
user selects how they want to sort. Based on that paramenter,I am also
groupting data.
I have code on the Table group header for the grouping and that works fine,
however I do not know where to add code to sort the table based on the
Sorting paramenter.
Anyone have any ideas '
Thanks
"armela" wrote:
> Hi there.
> I am using SQL 2005 SP1. I tried to access the website you referred to, but
> that did not take me anywhere.
> Can you give me website address again '
> Thanks
> "adolf garlic" wrote:
> > which version of reporting services are you using?
> >
> > if you are using 2005 then sorting is built in to the front end
> > see 'interactive sorting' at
> > ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/7eb8dcef-d152-470d-b440-3aaa2c26fb56.htm
> >
> >
> >
> > "armela" wrote:
> >
> > > Here is my question.
> > >
> > > Sorting and grouping question by allowing users to select the sorting field
> > >
> > >
> > >
> > > I have a report where I am giving the users a parameter so that they can
> > > select which field they would like to sort on.The report is also grouping by
> > > that field. I have a gruping section, where i have added code to group on the
> > > field I want based on this parameter, however I also would like to change the
> > > sorting order but I checked around and I did not find any info.
> > >
> > > So here is my example. I am showing sales order info.The user can sort and
> > > group by SalesPerson or Customer. Right now, I have code on my dataset to
> > > sort by SalesPerson Code and Order No. So far the grouping works, however the
> > > sorting does not.
> > > So when the users selects SalesPerson from the report parameter, it will
> > > sort by salesPerson and group by each salesperson, and when they select
> > > Customer, it should group and sort by customer. The grouping section already
> > > works, but I can not figure out where to change the sorting rule.
> > >
> > >
> > >
> > > Any suggestions would help.
> > >
> > >
> > >
> > >
> > >
> > >
> > >|||What I have understood, correct me if I am wrong.
You have grouping in your table which you have already given code for
grouping and when it groups as per the given column name, it has to be sorted
may be asc or desc order.
If you are grouping on a table column, the same table column should be
selected in the sorting tab of the table from the drop down.
correct me if I have understood wrongly.
Amarnath
"armela" wrote:
> Maybe you misunderstood my question
> I have a paramenter on the option's screen called sorting option and the
> user selects how they want to sort. Based on that paramenter,I am also
> groupting data.
> I have code on the Table group header for the grouping and that works fine,
> however I do not know where to add code to sort the table based on the
> Sorting paramenter.
> Anyone have any ideas '
> Thanks
> "armela" wrote:
> > Hi there.
> > I am using SQL 2005 SP1. I tried to access the website you referred to, but
> > that did not take me anywhere.
> > Can you give me website address again '
> > Thanks
> >
> > "adolf garlic" wrote:
> >
> > > which version of reporting services are you using?
> > >
> > > if you are using 2005 then sorting is built in to the front end
> > > see 'interactive sorting' at
> > > ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/7eb8dcef-d152-470d-b440-3aaa2c26fb56.htm
> > >
> > >
> > >
> > > "armela" wrote:
> > >
> > > > Here is my question.
> > > >
> > > > Sorting and grouping question by allowing users to select the sorting field
> > > >
> > > >
> > > >
> > > > I have a report where I am giving the users a parameter so that they can
> > > > select which field they would like to sort on.The report is also grouping by
> > > > that field. I have a gruping section, where i have added code to group on the
> > > > field I want based on this parameter, however I also would like to change the
> > > > sorting order but I checked around and I did not find any info.
> > > >
> > > > So here is my example. I am showing sales order info.The user can sort and
> > > > group by SalesPerson or Customer. Right now, I have code on my dataset to
> > > > sort by SalesPerson Code and Order No. So far the grouping works, however the
> > > > sorting does not.
> > > > So when the users selects SalesPerson from the report parameter, it will
> > > > sort by salesPerson and group by each salesperson, and when they select
> > > > Customer, it should group and sort by customer. The grouping section already
> > > > works, but I can not figure out where to change the sorting rule.
> > > >
> > > >
> > > >
> > > > Any suggestions would help.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >|||I figured this out.
It is in group properties, sorting tab.
that is where I added code to sort by the fields I needed by using the IIF
statement and checking the paramenter values.
THanks
"Amarnath" wrote:
> What I have understood, correct me if I am wrong.
> You have grouping in your table which you have already given code for
> grouping and when it groups as per the given column name, it has to be sorted
> may be asc or desc order.
> If you are grouping on a table column, the same table column should be
> selected in the sorting tab of the table from the drop down.
> correct me if I have understood wrongly.
> Amarnath
> "armela" wrote:
> > Maybe you misunderstood my question
> > I have a paramenter on the option's screen called sorting option and the
> > user selects how they want to sort. Based on that paramenter,I am also
> > groupting data.
> > I have code on the Table group header for the grouping and that works fine,
> > however I do not know where to add code to sort the table based on the
> > Sorting paramenter.
> > Anyone have any ideas '
> >
> > Thanks
> >
> > "armela" wrote:
> >
> > > Hi there.
> > > I am using SQL 2005 SP1. I tried to access the website you referred to, but
> > > that did not take me anywhere.
> > > Can you give me website address again '
> > > Thanks
> > >
> > > "adolf garlic" wrote:
> > >
> > > > which version of reporting services are you using?
> > > >
> > > > if you are using 2005 then sorting is built in to the front end
> > > > see 'interactive sorting' at
> > > > ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/7eb8dcef-d152-470d-b440-3aaa2c26fb56.htm
> > > >
> > > >
> > > >
> > > > "armela" wrote:
> > > >
> > > > > Here is my question.
> > > > >
> > > > > Sorting and grouping question by allowing users to select the sorting field
> > > > >
> > > > >
> > > > >
> > > > > I have a report where I am giving the users a parameter so that they can
> > > > > select which field they would like to sort on.The report is also grouping by
> > > > > that field. I have a gruping section, where i have added code to group on the
> > > > > field I want based on this parameter, however I also would like to change the
> > > > > sorting order but I checked around and I did not find any info.
> > > > >
> > > > > So here is my example. I am showing sales order info.The user can sort and
> > > > > group by SalesPerson or Customer. Right now, I have code on my dataset to
> > > > > sort by SalesPerson Code and Order No. So far the grouping works, however the
> > > > > sorting does not.
> > > > > So when the users selects SalesPerson from the report parameter, it will
> > > > > sort by salesPerson and group by each salesperson, and when they select
> > > > > Customer, it should group and sort by customer. The grouping section already
> > > > > works, but I can not figure out where to change the sorting rule.
> > > > >
> > > > >
> > > > >
> > > > > Any suggestions would help.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >

Allow null value in multi-value parameter

Hello,

I have a multi-value parameter field and each item comes from a table in the DB.

This multi-value parameter allows to select all or some of the items.

What if I don't want to select ANY item?

I read in the forum the several tray to do the same but the answers are not so clear..

If I say that at the end is a bug in SSRS... AM I right?

Thank you

Marina B.

I do this all the time. THe difference between what you have read and tried and what I am doing is that I am constructing the query as a string, not trying to pass the parameter values to a sproc or anything.

If you are interested in doing it this way, I think I wrote some instructions in other threads... try this one http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1

As to whether it is a "bug in SSRS", I'm not sure why you think that but I'm sure other people will be happy to debate it with you <g>.

>L<

Allow Blank Values for Strings

I have a data set in Reporting Services Report (2003)

if (@.prmAudit = 0 or @.prmCellType = 0)
BEGIN
select top 5000 * from NECCCUSTAUDIT.dbo.vw_cust_audit_billing_problem b where datediff(d,@.prmStartDate,b.fld_date_time) >=0 and datediff(d,@.prmEndDate,b.fld_date_time) <=0 and b.fld_problem_type like @.prmType + '%'
END
if (@.prmAudit = 1 or @.prmType = 0)
BEGIN
select top 5000 * from NECCCUSTAUDIT.dbo.vw_cust_audit_cell_problem b
where (DATEDIFF(d, fld_date_time, @.prmStartDate) <= 0)
AND (DATEDIFF(d, fld_date_time, @.prmEndDate) >= 0) and fld_problem_type not like 'A%'
AND b.fld_problem_type like @.prmCellType + '%'

END

--

When I preview my report

Enter the Fist Date, Last Date, prmAudit, prmType

>> I do not select anything for prmCellType

There is no data seen, it is totally blank.

>> When I select FistDate, LastDate, prmAudit, prmType and prmCellType

It works all fine.

Question: I want my query to accept @.prmType as blank, so I went to Reports-->ReportParameter and allowed for Blank. This parameter is a string.

Still my Report does not do what I want?

Please could somebody help me correct my query or give a better solution.

Thank you,

Does the "allow blank" option work in preview, but not when republishing the report on the report server?

In that case, you first have to delete the report from the report server and then publish again. If you just republish over an existing report, parameter settings will be merged with the previous parameter settings (and in this case, the allow blank option will be ignored).

-- Robert

ALL Option

After consulting in MSFT textbooks, I tried adding a UNION to my SELECT
statement for parameters list
Select Distinct Homebase, HomebaseName From dbo.Portal_D_PrvList
UNION Select -1,'All Locations'
It gives me an error message "Error converting NVARCHAR value to datatype
integer" My values in dbo.Portal_D_PrvList tables are nvarchar as they are
text items. Any ideas?
ThanksTry this
Union ALL
Select -1 as Homebase,'All Locations' AS HomebaseName
"Asim" wrote:
> After consulting in MSFT textbooks, I tried adding a UNION to my SELECT
> statement for parameters list
> Select Distinct Homebase, HomebaseName From dbo.Portal_D_PrvList
> UNION Select -1,'All Locations'
> It gives me an error message "Error converting NVARCHAR value to datatype
> integer" My values in dbo.Portal_D_PrvList tables are nvarchar as they are
> text items. Any ideas?
> Thanks
>
>|||Not working!
"johnE" wrote:
> Try this
> Union ALL
> Select -1 as Homebase,'All Locations' AS HomebaseName
> "Asim" wrote:
> > After consulting in MSFT textbooks, I tried adding a UNION to my SELECT
> > statement for parameters list
> > Select Distinct Homebase, HomebaseName From dbo.Portal_D_PrvList
> > UNION Select -1,'All Locations'
> > It gives me an error message "Error converting NVARCHAR value to datatype
> > integer" My values in dbo.Portal_D_PrvList tables are nvarchar as they are
> > text items. Any ideas?
> > Thanks
> >
> >
> >|||You are trying to put an integer (-1) unioned with string. Try this:
Select Distinct Homebase, HomebaseName From dbo.Portal_D_PrvList UNION
Select '-1' as Homebase,'All Locations' as HomebaseName
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:1B362CCD-3A3A-4E70-A26F-EAC2E8076D1F@.microsoft.com...
> After consulting in MSFT textbooks, I tried adding a UNION to my SELECT
> statement for parameters list
> Select Distinct Homebase, HomebaseName From dbo.Portal_D_PrvList
> UNION Select -1,'All Locations'
> It gives me an error message "Error converting NVARCHAR value to datatype
> integer" My values in dbo.Portal_D_PrvList tables are nvarchar as they are
> text items. Any ideas?
> Thanks
>
>|||Try
union
select convert(varchar, -1) as Homebase
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:2D8D2E8A-47F8-4866-9547-8B8A0EBA3A09@.microsoft.com...
> Not working!
>
> "johnE" wrote:
>> Try this
>> Union ALL
>> Select -1 as Homebase,'All Locations' AS HomebaseName
>> "Asim" wrote:
>> > After consulting in MSFT textbooks, I tried adding a UNION to my SELECT
>> > statement for parameters list
>> > Select Distinct Homebase, HomebaseName From dbo.Portal_D_PrvList
>> > UNION Select -1,'All Locations'
>> > It gives me an error message "Error converting NVARCHAR value to
>> > datatype
>> > integer" My values in dbo.Portal_D_PrvList tables are nvarchar as they
>> > are
>> > text items. Any ideas?
>> > Thanks
>> >
>> >
>> >

All DB ops hangs in sql2000

Hi
We have a warehouse fact table with millions of rows.
When we run a report that select massive amount of data
all the queries against that table hangs.
We checked there were no outstanding transactions against that table.
Any ideas what it could be.
Tks
MangeshMangesh Deshpande wrote:
> Hi
> We have a warehouse fact table with millions of rows.
> When we run a report that select massive amount of data
> all the queries against that table hangs.
> We checked there were no outstanding transactions against that table.
> Any ideas what it could be.
> Tks
> Mangesh
Are any of the queries timing out, possibly leaving locks on the server
until the connection is cleaned up by SQL Server?
What about blocking issues? Can you use a NOLOCK hint on the "massive"
queries to prevent shared locks from being taken?
David Gugick
Imceda Software
www.imceda.com

ALL and empty set

SQL Server 2000 SP4. Hoping to find a logical explanation for a certain
behavior.

Consider this script:

USE pubs
GO

IF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = 'CA')
PRINT 'TRUE'
ELSE
PRINT 'FALSE'

This, as expected, prints FALSE, since not all authors in CA are under
contract. Now, if the script is changed as follows:

USE pubs
GO

IF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = '')
PRINT 'TRUE'
ELSE
PRINT 'FALSE'

then the result is TRUE. In other words, the expression evaluates to TRUE
when the select statement produces an empty set, which doesn't make sense
to me. Even more interesting, the expression

NULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')

still evaluates to TRUE (ANSI_NULLS is ON).

Can anyone explain these results? Is this the expected behavior in the SQL
standard, or something that is specific to SQL Server? Thanks.

--
remove a 9 to reply by emailDimitri Furman wrote:

Quote:

Originally Posted by

Consider this script:
>
USE pubs
GO
>
IF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = 'CA')
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
>
This, as expected, prints FALSE, since not all authors in CA are under
contract. Now, if the script is changed as follows:
>
USE pubs
GO
>
IF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = '')
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
>
then the result is TRUE. In other words, the expression evaluates to TRUE
when the select statement produces an empty set, which doesn't make sense
to me. Even more interesting, the expression
>
NULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')
>
still evaluates to TRUE (ANSI_NULLS is ON).
>
Can anyone explain these results? Is this the expected behavior in the SQL
standard, or something that is specific to SQL Server? Thanks.

It's vacuously true because there are no elements in the set to
produce a non-equality.|||On 29.09.2006 05:24, Dimitri Furman wrote:

Quote:

Originally Posted by

SQL Server 2000 SP4. Hoping to find a logical explanation for a certain
behavior.
>
Consider this script:
>
USE pubs
GO
>
IF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = 'CA')
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
>
This, as expected, prints FALSE, since not all authors in CA are under
contract. Now, if the script is changed as follows:
>
USE pubs
GO
>
IF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = '')
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
>
then the result is TRUE. In other words, the expression evaluates to TRUE
when the select statement produces an empty set, which doesn't make sense
to me. Even more interesting, the expression
>
NULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')
>
still evaluates to TRUE (ANSI_NULLS is ON).
>
Can anyone explain these results? Is this the expected behavior in the SQL
standard, or something that is specific to SQL Server? Thanks.

I think that's general boolean logic. If you evaluate AND over a set of
items you start with TRUE and go until you reach the first FALSE. In
Ruby:

Quote:

Originally Posted by

Quote:

Originally Posted by

>def and_all(items)
>items.each {|i| return false unless i}
>true
>end

=nil

Quote:

Originally Posted by

Quote:

Originally Posted by

>and_all [true, true, true]

=true

Quote:

Originally Posted by

Quote:

Originally Posted by

>and_all [true, true]

=true

Quote:

Originally Posted by

Quote:

Originally Posted by

>and_all [true]

=true

Quote:

Originally Posted by

Quote:

Originally Posted by

>and_all []

=true

Quote:

Originally Posted by

Quote:

Originally Posted by

>and_all [true, false, true]

=false

Quote:

Originally Posted by

Quote:

Originally Posted by

>and_all [true, true, false]

=false

Kind regards

robert|||Dimitri,

I do not think it is a good idea to use ALL clause in production ever,
because it is not intuitive to understand.
The mainstream approach is to use IN and/or EXISTS and/or JOIN.
Also note that queries using mainstream features have a better chance
to get a decent execution plan.

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||On Fri, 29 Sep 2006 03:24:09 -0000, Dimitri Furman wrote:

(snip)

Quote:

Originally Posted by

the expression evaluates to TRUE
>when the select statement produces an empty set, which doesn't make sense
>to me.

Hi Dimitri,

Hmm, it actuallly makes perfect sense to me. This is a bit like me
bragging that I've slain every single dragon that has ever set fooot in
my back yard. And that I've rescued every princess that has been locked
away in a high tower during my life time.

Of course, I've never fought a dragon or rescued a princess - but since
no dragon has ever set foot in my back yard (see! they are THAT afraid
of me <g>) and no princess has been locked away in a high tower during
my life time, the statements above are still true.

Quote:

Originally Posted by

Even more interesting, the expression
>
>NULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')

This is an interesting, since you can defend two answers. The simple
version is that any comparison involving NULL should result in unknown.
The other, almost equally simple, version is that if the subquery
produces an empty set, it doesn;t matter what value is on the left-hand
side; we can be sure that it'll be equal to all values in the empty set.
So we don't care if the value for the left-hand side is supplied or
missing, since we can evaluate anyhow.

Since both definitions have their merit, we'll have to resort to
consulting the documentation. Books Online says this about ALL:

"Returns TRUE when the comparison specified is TRUE for all pairs
(scalar_expression, x), when x is a value in the single-column set;
otherwise returns FALSE."

Since there are no values in the set, there are no pairs - just as there
are no dragons in my back yard.

To double-check, I consulted the description of ALL in the ISO standard
SQL-2003. That one puts it even more bluntly, since it excplicitly
mentions that case that the subquery returns an empty set:

"Case:
"a) If T is empty or if the implied <comparison predicateis True for
every row RT in T, then R <comp op<allT is True."

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,

On Sep 29 2006, 05:24 pm, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALIDwrote in
news:ap2rh2540dnp6ssibojofp8fcdv73cn4mp@.4ax.com:

Quote:

Originally Posted by

On Fri, 29 Sep 2006 03:24:09 -0000, Dimitri Furman wrote:
>

Quote:

Originally Posted by

>Even more interesting, the expression
>>
>>NULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')

>
This is an interesting, since you can defend two answers. The simple
version is that any comparison involving NULL should result in
unknown. The other, almost equally simple, version is that if the
subquery produces an empty set, it doesn;t matter what value is on the
left-hand side; we can be sure that it'll be equal to all values in
the empty set. So we don't care if the value for the left-hand side is
supplied or missing, since we can evaluate anyhow.

All right, this is not too surprising considering how "consistently" NULLs
are treated in SQL. I guess we can write it off as another example. Now
when anyone says that a NULL is not equal to anything, I'll have a
valid objection! :)

Quote:

Originally Posted by

Since both definitions have their merit, we'll have to resort to
consulting the documentation. Books Online says this about ALL:
>
"Returns TRUE when the comparison specified is TRUE for all pairs
(scalar_expression, x), when x is a value in the single-column set;
otherwise returns FALSE."

This is what bothers me. In the case of an empty set there are no pairs,
and the comparisons cannot be either TRUE or FALSE - there can be no
comparisons in the first place - so according to the above, this should
fall under "otherwise" and return FALSE.

Quote:

Originally Posted by

To double-check, I consulted the description of ALL in the ISO
standard SQL-2003. That one puts it even more bluntly, since it
excplicitly mentions that case that the subquery returns an empty set:
>
"Case:
"a) If T is empty or if the implied <comparison predicateis True for
every row RT in T, then R <comp op<allT is True."

Well, this settles it. Thanks for digging this up - that's really the
answer I was looking for.

--
remove a 9 to reply by email