degree of seperation

Hi,
how can I efficiently find out the shortest connection in the following scenario:
there's a table which has two colums, both of the same type.
two items in the same row means that they are connected. (there is no direction of the connection so that a connection shows up in both ways in a table for query performance issues)
ie. item1 is connected with item2 and item2 is connected with item3, then the table looks like:

row1: item1 , item2
row2: item2 , item1

row3: item2 , item3
row4: item3 , item2

so the connection item1 to item3 would be item1 - item2 - item3.
degree of seperation is 2 in this case. how can I implement this in general with sql?
making an exhaustive search would cost too much time. thanks for any hints!

Perhaps you could dream up a recursive CTE to do this, although I can't think how. You could write a SQL CLR routine to do itwith the CLR it should be easy enough to do a search of the graph.

What confuses me is that you don't want "an exhaustive search". There's no magic here: assuming the shortest path is of length n, I don't see a way to get away without considering all paths shorter than n in one way or another.

Cheers,

Defining a Query in a Table Adapter

Now, I don't know if what I want to do is possible, but here goes. In the table I want to query, there is an "approval status" column, of type Int32. There are four approval levels, 1, 2, 3 and 4. What I want to set up is a query in the table adapter that can return all entries of one or more approval levels. In "raw" sql, I would do something like:

SELECT * FROM facility_table WHERE (approved IN (1,2,3));

What I want to do though, is to have the list of approval codes to be a parameter that I can pass to the table adapter query, so the where clause becomes "WHERE (approved IN (@.approval))", and I pass a string with the list of approval codes. But the query designer doesn't want to cooperate with me, as it insists that "@.approval" should be an int32.

Any suggestions?

If you have the flexibility to do so, I would recommend changing the datatype to string. Remember you'll need to delimit the individual values when you pass them in - '1','2','3'.

Good luck.

define query parameters ?

when I'm defining a dataset based on stored procedure I type in the name of
my stored proc and hit the !
then define query parameters comes up. My stored proc has default values
defined for the parameter. Why not have the define query parameters fill in
those default values? It's really anoying to type in 15 parameters almost
everytime I need to re-run the query.HI,
"letuce dance" <letucedance@.discussions.microsoft.com> schrieb im
Newsbeitrag news:C5B2E705-D5B4-47F7-8595-FDC9266B917F@.microsoft.com...
> when I'm defining a dataset based on stored procedure I type in the name
> of
> my stored proc and hit the !
> then define query parameters comes up. My stored proc has default values
> defined for the parameter. Why not have the define query parameters fill
> in
> those default values? It's really anoying to type in 15 parameters almost
> everytime I need to re-run the query.
you can set the default values in the report itself.
witch to layout, click on the upper left corner (report properties), context
menu "report parameters" and there you can set the default settings
"not-queried" and store all your default-parameter values.
hth, Tony

Define a Key - "select * from "

Is there a way to define a key that puts the text 'SELECT * FROM '
into the Query Analyzer window?
I must type this about 50 times a day but cannot see a simple way of
defining a key to write it for me..
(tools/customize) seems to execute everything you put in there rather
than leave it on the screen for me to add table names etc to.

thanks for your time...On Jul 9, 10:39 am, Stagnight1 <stagnig...@.yahoo.comwrote:

Quote:

Originally Posted by

Is there a way to define a key that puts the text 'SELECT * FROM '
into the Query Analyzer window?
I must type this about 50 times a day but cannot see a simple way of
defining a key to write it for me..
(tools/customize) seems to execute everything you put in there rather
than leave it on the screen for me to add table names etc to.
>
thanks for your time...

You can assign it to Ctrl-V by putting it on the clipboard.|||Stagnight1 wrote:

Quote:

Originally Posted by

Is there a way to define a key that puts the text 'SELECT * FROM '
into the Query Analyzer window?
I must type this about 50 times a day but cannot see a simple way of
defining a key to write it for me..
(tools/customize) seems to execute everything you put in there rather
than leave it on the screen for me to add table names etc to.

I haven't tried it, but
http://www.regsoft.com/keys/
others may be available
http://www.google.com/search?hl=en&...G=Google+Search
Andrew

Default values for SQL Server Data Types

Hi,

I need to populate some SqlParameter but I temporarily need to populate with a default value for the data type in question. Of course I can work them out, but I was wondering if there is already a way of doing this, from the .Net Classes or from SQL Server but without making a trip to the DB.

Thanks

John

ADO.NET does not have a feature where you can mark a parameter as default (unfortunately).

In general if you don't send the parameter, then the server side default will be used, so you could just omit the parameter to get the default, then add parameters later when you have values to supply. But this may mean rebuilding your parameters collection.

Also, if you need to fetch the actual defaults for a stored procedure parameters, then there is no choice but to make a trip to the server. ADO.NET is not magic. (G)

Default values for datetime parameters

Hi all,

I have a report that needs a to-date and a from-date as input. I would like them to be set to today by default.

I have set the data type for both parameters to DateTime and the default value as "Non-queried" = Today()

This does fill in the dates with today, but I cannot change the value in the fields, until the report has been generated once. I just want the date to be equal to today since 90% of the time that is what my users wants to see - but I want to give the last 10% the chance to change the date without having to generate the report first.

The date-fields are locked until some of the other parameters are filled in... It makes no sense really.

Hope you can help me here :-D

-- Heidi, Denmark

I have the following report parameters set that generate default From & To dates as the previous months 1st & Last Day. Both Available Parameters & Default Parameters are set to "Non-Queried"

The third parameter (groupid) defaults to a Null value but user can choose from a list of valid values.

When I deploy this report, the user can change the From & To dates without first running the report.

I'll attach the parameters XML

<ReportParameters>

<ReportParameter Name="startdate">

<DataType>DateTime</DataType>

<DefaultValue>

<Values>

<Value>=dateadd("m",-1,dateadd("d",-day(today)+1,today))</Value>

</Values>

</DefaultValue>

<Prompt>Select From Date</Prompt>

</ReportParameter>

<ReportParameter Name="enddate">

<DataType>DateTime</DataType>

<DefaultValue>

<Values>

<Value>=dateadd("d",-day(today),today)</Value>

</Values>

</DefaultValue>

<Prompt>Select To Date</Prompt>

</ReportParameter>

<ReportParameter Name="groupid">

<DataType>String</DataType>

<Prompt>Select Hierarchy Group</Prompt>

<ValidValues>

<DataSetReference>

<DataSetName>H_Groups</DataSetName>

<ValueField>id</ValueField>

<LabelField>List</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

</ReportParameters>

Does this help at all?

|||

Hi,

I've tried setting the dates like you do, but that doesn't help me. The user still isn't able to change the date values.

I have 6 parameters:

"Product" e.g. Ham (character)

"Batch" e.g. 121434 (character)

"From date" (datetime)

"From time" 05:00 (character)

"To date" e.g. (datetime)

"To time" e.g. 11:30 (character)

I only have default values for the date fields. The user cannot change the date values until he has entered something in the other parameters. It is like the date fields are disabled somehow and the default date isn't even displayed to the user (the field is simply grey...)

I suspect that it is because I use built-in functions instead of data from a query (I've tried making an sql selecting today, but I cannot really get that to work - problems with the formatting) :-S

|||

Heidi Christiansen wrote:

I suspect that it is because I use built-in functions instead of data from a query (I've tried making an sql selecting today, but I cannot really get that to work - problems with the formatting) :-S

I'm using built-in functions as well, so I don't think that's it.

Try making your date parameters the first ones to be chosen. I think this fixes it for you, although I don't really understand why this should be the case. I suspect it would be "by design"

|||

LOL

That helped... strange design decisions....

Thank you for your help :-D

default values

Is it possible to set report parameter of type datetime on current date in case when user don't specify value?
Thx.Yes you can and it's dead easy.
In the Report Parameters dialog, in the Default Values section at the borrom of the form, select Non-queried and use the following expression:
=Globals!ExecutionTime
That'll insert the exact date and time of when the report is run as a default value if one isn't entered.
DF
"AG, NLB d.d." wrote:
> Is it possible to set report parameter of type datetime on current date in case when user don't specify value?
> Thx.|||is it possible to make yesterday as default value?
>--Original Message--
>Yes you can and it's dead easy.
>In the Report Parameters dialog, in the Default Values
section at the borrom of the form, select Non-queried and
use the following expression:
>=Globals!ExecutionTime
>That'll insert the exact date and time of when the report
is run as a default value if one isn't entered.
>DF
>"AG, NLB d.d." wrote:
>> Is it possible to set report parameter of type datetime
on current date in case when user don't specify value?
>> Thx.
>.
>

Default value of a GUID field?!

I have a column named "ID" and its type is "uniqueidentifier" in SQL Server
2005 Express.
this column is the primary key and its default value is "newid()" in SQL
Server.
I have generated a DataSet in C# that has this column but its default value
is DBNull what can I do to make the default value be the return value of
this function ( or I want the Id be generated by default in the DataSet)?
System.Guid.NewGuid()
H? Leon
Have you cons?dered to create the guid from code and then insert it into the
table, in stead of letting the sql server generate it for you ?. Otherwise it
is possible to query the table for the inserted row, and reading the guid.
(if the guid isn't the primary key)
Hope this helps.
TIA
/Allan
"Leon_Amirreza" wrote:

> I have a column named "ID" and its type is "uniqueidentifier" in SQL Server
> 2005 Express.
> this column is the primary key and its default value is "newid()" in SQL
> Server.
> I have generated a DataSet in C# that has this column but its default value
> is DBNull what can I do to make the default value be the return value of
> this function ( or I want the Id be generated by default in the DataSet)?
> System.Guid.NewGuid()
>
>
|||Thanks but this gets very tricky
and sorry for the clock problem
"Allan Bentsen" <Allan.Bentsen@.discussions.microsoft.com> wrote in message
news:6FDCD5BC-DC5F-4FC9-A4E4-D23E855F651F@.microsoft.com...[vbcol=seagreen]
> H? Leon
> Have you cons?dered to create the guid from code and then insert it into
> the
> table, in stead of letting the sql server generate it for you ?. Otherwise
> it
> is possible to query the table for the inserted row, and reading the guid.
> (if the guid isn't the primary key)
> Hope this helps.
> --
> TIA
> /Allan
>
> "Leon_Amirreza" wrote:
|||becuase I am using offline Typed DataSets and the GUID field is the Primary
Key of the table
"Leon_Amirreza" <amirreza_rahmaty@.yahoo.com> wrote in message
news:uhK4HNz%23GHA.4472@.TK2MSFTNGP05.phx.gbl...
> Thanks but this gets very tricky
> and sorry for the clock problem
> "Allan Bentsen" <Allan.Bentsen@.discussions.microsoft.com> wrote in message
> news:6FDCD5BC-DC5F-4FC9-A4E4-D23E855F651F@.microsoft.com...
>
|||Ouch!! That's not a good idea.
GUID -Identity and Primary Keys
http://sqlteam.com/item.asp?ItemID=2599
GUID -Is not Always GOOD
http://bloggingabout.net/blogs/wellink/archive/2004/03/15/598.aspx
GUID -The Cost of GUIDs as Primary Keys
http://www.informit.com/articles/article.asp?p=25862&rl=1
GUID -Uniqueidentifier vs. IDENTITY
http://sqlteam.com/item.asp?ItemID=283
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leon_Amirreza" <amirreza_rahmaty@.yahoo.com> wrote in message
news:O1BfFPz%23GHA.3352@.TK2MSFTNGP03.phx.gbl...
> becuase I am using offline Typed DataSets and the GUID field is the
> Primary Key of the table
> "Leon_Amirreza" <amirreza_rahmaty@.yahoo.com> wrote in message
> news:uhK4HNz%23GHA.4472@.TK2MSFTNGP05.phx.gbl...
>

default value not function

I have set a column A in table test with default value = 0 (money data type)
and not allow null, but when I run my application and want to save the
record, there is an error like ...cannot
insert value Null into column A table test, column does not allow null,
insert fail..., why the default value 0 not function (why there is null
insert?, not 0 insert)
Hi
The default will only be used if you don't specify a value for the column or
if you use the default keyword.
e.g.
CREATE TABLE MyTest ( id int not null ,
val char(1) not null default 'A' )
INSERT INTO MyTest ( id ) VALUES ( 1 )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 2, 'C' )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 3, DEFAULT )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 4, NULL )
/*
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'val', table 'tempdb.dbo.MyTest';
column does not allow nulls. INSERT fails.
The statement has been terminated.
*/
INSERT INTO MyTest ( id, val ) VALUES ( 4, ISNULL(NULL,'A') )
--(1 row(s) affected)
SELECT * FROM MyTest
/*
id val
-- --
1 A
2 C
3 A
4 A
(4 row(s) affected)
*/
John
"tong" wrote:

> I have set a column A in table test with default value = 0 (money data type)
> and not allow null, but when I run my application and want to save the
> record, there is an error like ...cannot
> insert value Null into column A table test, column does not allow null,
> insert fail..., why the default value 0 not function (why there is null
> insert?, not 0 insert)
>

default value not function

I have set a column A in table test with default value = 0 (money data type)
and not allow null, but when I run my application and want to save the
record, there is an error like ...cannot
insert value Null into column A table test, column does not allow null,
insert fail..., why the default value 0 not function (why there is null
insert', not 0 insert)Hi
The default will only be used if you don't specify a value for the column or
if you use the default keyword.
e.g.
CREATE TABLE MyTest ( id int not null ,
val char(1) not null default 'A' )
INSERT INTO MyTest ( id ) VALUES ( 1 )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 2, 'C' )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 3, DEFAULT )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 4, NULL )
/*
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'val', table 'tempdb.dbo.MyTest';
column does not allow nulls. INSERT fails.
The statement has been terminated.
*/
INSERT INTO MyTest ( id, val ) VALUES ( 4, ISNULL(NULL,'A') )
--(1 row(s) affected)
SELECT * FROM MyTest
/*
id val
-- --
1 A
2 C
3 A
4 A
(4 row(s) affected)
*/
John
"tong" wrote:
> I have set a column A in table test with default value = 0 (money data type)
> and not allow null, but when I run my application and want to save the
> record, there is an error like ...cannot
> insert value Null into column A table test, column does not allow null,
> insert fail..., why the default value 0 not function (why there is null
> insert', not 0 insert)
>

default value not function

I have set a column A in table test with default value = 0 (money data type)
and not allow null, but when I run my application and want to save the
record, there is an error like ...cannot
insert value Null into column A table test, column does not allow null,
insert fail..., why the default value 0 not function (why there is null
insert', not 0 insert)Hi
The default will only be used if you don't specify a value for the column or
if you use the default keyword.
e.g.
CREATE TABLE MyTest ( id int not null ,
val char(1) not null default 'A' )
INSERT INTO MyTest ( id ) VALUES ( 1 )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 2, 'C' )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 3, DEFAULT )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 4, NULL )
/*
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'val', table 'tempdb.dbo.MyTest';
column does not allow nulls. INSERT fails.
The statement has been terminated.
*/
INSERT INTO MyTest ( id, val ) VALUES ( 4, ISNULL(NULL,'A') )
--(1 row(s) affected)
SELECT * FROM MyTest
/*
id val
-- --
1 A
2 C
3 A
4 A
(4 row(s) affected)
*/
John
"tong" wrote:

> I have set a column A in table test with default value = 0 (money data typ
e)
> and not allow null, but when I run my application and want to save the
> record, there is an error like ...cannot
> insert value Null into column A table test, column does not allow null,
> insert fail..., why the default value 0 not function (why there is null
> insert', not 0 insert)
>

default value for todays date and time

I have a column 'date and time', of type datetime.
I'm using MS VB Express - and am trying to enter a default value for
the column of today's date and time.
I cant find anything to enter in the 'default' property of the column
that will return todays date and time.
please help.
gary.
try either the function getdate() or CURRENT_TIMESTAMP
<garyusenet@.myway.com> wrote in message
news:1130144997.367325.64910@.f14g2000cwb.googlegro ups.com...
>I have a column 'date and time', of type datetime.
>
> I'm using MS VB Express - and am trying to enter a default value for
> the column of today's date and time.
>
> I cant find anything to enter in the 'default' property of the column
> that will return todays date and time.
>
> please help.
>
> gary.
>

default value for todays date and time

I have a column 'date and time', of type datetime.
I'm using MS VB Express - and am trying to enter a default value for
the column of today's date and time.
I cant find anything to enter in the 'default' property of the column
that will return todays date and time.
please help.
gary.try either the function getdate() or CURRENT_TIMESTAMP
<garyusenet@.myway.com> wrote in message
news:1130144997.367325.64910@.f14g2000cwb.googlegroups.com...
>I have a column 'date and time', of type datetime.
>
> I'm using MS VB Express - and am trying to enter a default value for
> the column of today's date and time.
>
> I cant find anything to enter in the 'default' property of the column
> that will return todays date and time.
>
> please help.
>
> gary.
>

default value for todays date and time

I have a column 'date and time', of type datetime.
I'm using MS VB Express - and am trying to enter a default value for
the column of today's date and time.
I cant find anything to enter in the 'default' property of the column
that will return todays date and time.
please help.
gary.try either the function getdate() or CURRENT_TIMESTAMP
<garyusenet@.myway.com> wrote in message
news:1130144997.367325.64910@.f14g2000cwb.googlegroups.com...
>I have a column 'date and time', of type datetime.
>
> I'm using MS VB Express - and am trying to enter a default value for
> the column of today's date and time.
>
> I cant find anything to enter in the 'default' property of the column
> that will return todays date and time.
>
> please help.
>
> gary.
>

default value for integer

I had a field called camp is integer data type. I just found it had a default value, but the value is " (0) ", not just " 0 ". It should be 0, right? why it use (0), are they same? thanks.I had a field called camp is integer data type. I just found it had a default value, but the value is " (0) ", not just " 0 ". It should be 0, right? why it use (0), are they same? thanks.
And if you insert a new record with no value in the field then select that record what is the value?

Sql stores the default and displays it that way No biggy|||I don't understand what you mean. If no value, i would like to have value 0, but why it use (0)?|||It's just part of the syntax

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(
Col1 int IDENTITY(1,1) NOT NULL PRIMARY KEY
, Col2 int NOT NULL DEFAULT(0)
, Col3 char(1)
)
GO

INSERT INTO myTable99(Col3) SELECT 'x'

SELECT * FROM myTable99

INSERT INTO myTable99(Col2,Col3) SELECT 1,'y'

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

And stop using EM, you won't learn much using it...Do you have Books Online?|||If using query Analayzer, we have to use (0). If i set default value in the Enterprise Manager, i just open the table in design view, and then just type 0 in the default, not type (0) in the default, is that correct? I don't have book online, can i check online? Thanks|||Thanks, i got it, it is just syntax. Thanks all.|||I did set the default value 0 on the check box fileld, but when i check the data in the back end, it still has null value, why? Also in the front end, i set the default value to 0, it still didn't work, always give you null value if you didn't check it. But if i check it and then uncheck it. It will give you 0 value. How can i do so the if you didn't touch the check box, it will give you 0 value automatically? Thanks.|||If it was NULL BEFORE you set the default it will reamin NULL

You need to do an update statement.

Stop using EM

If you have Enterprise Manager, the you most likely have Books Online as well...

Default restrictions and field names...

Hello,
I'm in the task of changing the data type of a column in a SQL Server
2000 database which already has default and check constraints.
Being unable to directly issue an ALTER TABLE command, I guess that the
only way will be to delete the check constraints, the default
constraints, change the data type and then recreate the check and
default constraints updated.
The names of the check constraints and their corresponding columns could
be obtained from the CONSTRAINT_COLUMN_USAGE view. But I still can't get
any system view that related the name of the default constraints and the
name of their corresponding table columns.
Any help will be much appreciated,
KlermanHi
CREATE TABLE Test
(
col DATETIME DEFAULT GETDATE()
)
SELECT scobj.name, cols.name
FROM sysconstraints sc
INNER JOIN sysobjects scobj
ON sc.constid = scobj.id
AND sc.id=OBJECT_ID('Test')
INNER JOIN syscolumns cols
ON sc.id = cols.id
AND sc.colid = cols.colid
GO
ALTER TABLE Test DROP CONSTRAINT DF__Test__col__6C190EBB
"Klerman Gutierrez" <klerman.gutierrez@.gmail.com> wrote in message
news:e2KvNGllFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm in the task of changing the data type of a column in a SQL Server 2000
> database which already has default and check constraints.
> Being unable to directly issue an ALTER TABLE command, I guess that the
> only way will be to delete the check constraints, the default constraints,
> change the data type and then recreate the check and default constraints
> updated.
> The names of the check constraints and their corresponding columns could
> be obtained from the CONSTRAINT_COLUMN_USAGE view. But I still can't get
> any system view that related the name of the default constraints and the
> name of their corresponding table columns.
> Any help will be much appreciated,
> Klerman|||Uri,
Based on your suggestion, I wrote this solution. Hope this could help
someone else trying to remove restrictions associated with a field.
Thanks a lot for the help
Klerman
EXEC pa_BorraRestricciones 'TableName', 'ColumnName'
CREATE PROCEDURE pa_BorraRestricciones(@.Tabla SYSNAME, @.Columna SYSNAME)
AS
DECLARE @.Restriccion SYSNAME,
@.strSQL NVARCHAR(2048)
DECLARE Restricciones CURSOR LOCAL FAST_FORWARD FOR SELECT
SYSOBJECTS.NAME FROM (SYSCONSTRAINTS INNER JOIN SYSOBJECTS ON
SYSCONSTRAINTS.constid=SYSOBJECTS.id) INNER JOIN SYSCOLUMNS ON
SYSCONSTRAINTS.colid=SYSCOLUMNS.colid WHERE
SYSCONSTRAINTS.id=OBJECT_ID(@.Tabla) AND SYSCOLUMNS.NAME=@.Columna
OPEN Restricciones
FETCH NEXT FROM Restricciones INTO @.Restriccion
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SET @.strSQL = N'ALTER TABLE ' + @.Tabla + ' DROP CONSTRAINT ' +
@.Restriccion
EXEC (@.strSQL)
FETCH NEXT FROM Restricciones INTO @.Restriccion
END
CLOSE Restricciones
DEALLOCATE Restricciones
RETURN