Binary or Byte data linked to Checkbox

Dear Group,
I'm using Access 2003 linked to an SQL Server data table via an ODBC
connection.
On one form I have several check boxes which want data mapped to a
TRUE/FALSE datatype. Is there a direct way to bind a checkbox to a
bit, a binary, or a tinyint? I've only been able to get it to work
with a smallint which is 2 bytes long.
Thanks,
Max
Arg. Access wants to insert -1 for true and 0 for false.
So sorry the type will need to support negative numbers.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Max Yaffe" <myaffe@.not.gamry.com> wrote in message
news:0f1ji1p22uleclnn5t4lka3vnta48bda3k@.4ax.com...
> Dear Group,
> I'm using Access 2003 linked to an SQL Server data table via an ODBC
> connection.
> On one form I have several check boxes which want data mapped to a
> TRUE/FALSE datatype. Is there a direct way to bind a checkbox to a
> bit, a binary, or a tinyint? I've only been able to get it to work
> with a smallint which is 2 bytes long.
> Thanks,
> Max

Binary or Byte data linked to Checkbox

Dear Group,
I'm using Access 2003 linked to an SQL Server data table via an ODBC
connection.
On one form I have several check boxes which want data mapped to a
TRUE/FALSE datatype. Is there a direct way to bind a checkbox to a
bit, a binary, or a tinyint? I've only been able to get it to work
with a smallint which is 2 bytes long.
Thanks,
MaxArg. Access wants to insert -1 for true and 0 for false.
So sorry the type will need to support negative numbers.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Max Yaffe" <myaffe@.not.gamry.com> wrote in message
news:0f1ji1p22uleclnn5t4lka3vnta48bda3k@.
4ax.com...
> Dear Group,
> I'm using Access 2003 linked to an SQL Server data table via an ODBC
> connection.
> On one form I have several check boxes which want data mapped to a
> TRUE/FALSE datatype. Is there a direct way to bind a checkbox to a
> bit, a binary, or a tinyint? I've only been able to get it to work
> with a smallint which is 2 bytes long.
> Thanks,
> Max

BIDS, SP1, & Installation Account

On a "day to day" basis, the account I use to do work is not a member of the
Local Administrators group. When I do an install, I'll either do a Run As or
log on as a Local Administrator account to do the install.
On an XP Pro client (1GB RAM, 20 GB HDD), I install the SQL Client tools and
then Visual Studio 2005. All the Business Intelligence Project types work
correctly for both the installation account and the "day to day" account.
When I update the client tools to SQL SP1, the only user that can open a
Business Intelligence Projects is the Administrative account that did the
installation.
Using the "day to day" account, when I try to open an Analysis Services
Project, I get the error:
The Application for Project 'C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\PrivateAssemblies\Business Intelligence
Projects\AnalysisServicesProject.dwproj' is not installed.
Make sure the application for the type (.dwproj) is installed.
If the "day to day" account selects a different Business Intelligence
Project type, I get the error:
Object Reference not set to an instance of an Object
I can log on as the installation Administrator to do SQL work, but that
seems to me to be a security risk.
Any ideas on how to get the "day to day" account to work with Business
Intelligence projects? It works fine with all other project types in Visual
Studio?
Al
Sorry, I forgot to day, SQL Server 2005 Enterprise Edition with Client tools
loaded onto XP (SP2) from the Enterprise Edition CD. SQL 2005 SP1 installed
from SQLServer2005SP1-KB913090-x86-ENU.exe
"Al" <zyck@.tconl.com> wrote in message
news:uZ7Lv2NGHHA.2112@.TK2MSFTNGP03.phx.gbl...
> On a "day to day" basis, the account I use to do work is not a member of
> the Local Administrators group. When I do an install, I'll either do a Run
> As or log on as a Local Administrator account to do the install.
> On an XP Pro client (1GB RAM, 20 GB HDD), I install the SQL Client tools
> and then Visual Studio 2005. All the Business Intelligence Project types
> work correctly for both the installation account and the "day to day"
> account. When I update the client tools to SQL SP1, the only user that can
> open a Business Intelligence Projects is the Administrative account that
> did the installation.
> Using the "day to day" account, when I try to open an Analysis Services
> Project, I get the error:
> The Application for Project 'C:\Program Files\Microsoft Visual Studio
> 8\Common7\IDE\PrivateAssemblies\Business Intelligence
> Projects\AnalysisServicesProject.dwproj' is not installed.
> Make sure the application for the type (.dwproj) is installed.
>
> If the "day to day" account selects a different Business Intelligence
> Project type, I get the error:
>
> Object Reference not set to an instance of an Object
>
> I can log on as the installation Administrator to do SQL work, but that
> seems to me to be a security risk.
> Any ideas on how to get the "day to day" account to work with Business
> Intelligence projects? It works fine with all other project types in
> Visual Studio?
> Al
>

BIDS doesn't install correctly...

hi, (this is also posted on the vstudio.general group)
i installed the SQL 2005 toolkit so i could design reports in VS 2005, with
the Business Intelligence project templates.
my VS 2005 professional is installed into C:\Program Files\Microsoft Visual
Studio 2005\, i can't remember if this was the default directory or not, i
suspect it is not because of some "assembly not found" errors when i attempt
to run the so-called "Business Intelligence Development Studio" (which
installed with a broken short-cut after installation). i fixed the broken
shortcut to point to the visual studio 2005 devenv.exe.
i then try to create a new BI project and get a message
"Could not load file or assembly Microsoft.ReportingServices.Designer
Version=9.0.242.0"
another user also encountered this problem and posted it here:
http://forums.microsoft.com/MSDN/Sho...65224&SiteID=1
i searched for the Microsoft.ReportingServices.Designer file and it is
located in
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
i copy it to C:\Program Files\Microsoft Visual Studio
2005\Common7\IDE\PrivateAssemblies
and then restart VS and lo and behold i get a different error message for
the next file that is missing. so i try copying the entire
PrivateAssemblies folder in to the 2005 directory and now i get "object
reference not set to instance of an object". ok so i'm chancing my arm
copying files around like this, but can anyone make sense of the MESS that
is BIDS? i have repaired both my VS 2005 installation and all SQL 2005
related software, to no avail.
i would suspect that re-installing VS into the vs 8 directory (not the 2005
directory) would solve the problem but i would hate to waste all that time
uninstalling and reinstalling VS just because BIDS has a big installation
bug.
thanks for any help
tim mackey.
after i copied the PrivateAssemblies folder on top of my vS 2005 folder, it
got a bit worse (understandably) so i decided to do a repair. now the BI
projects work fine.
i'm still convinced there is a bug in BIDS integration with existing VS 2005
installations.
tim
"Tim_Mac" <tim.mackey@.community.nospam> wrote in message
news:e%23C%23f6r7GHA.3340@.TK2MSFTNGP04.phx.gbl...
> hi, (this is also posted on the vstudio.general group)
> i installed the SQL 2005 toolkit so i could design reports in VS 2005,
> with
> the Business Intelligence project templates.
> my VS 2005 professional is installed into C:\Program Files\Microsoft
> Visual
> Studio 2005\, i can't remember if this was the default directory or not, i
> suspect it is not because of some "assembly not found" errors when i
> attempt
> to run the so-called "Business Intelligence Development Studio" (which
> installed with a broken short-cut after installation). i fixed the broken
> shortcut to point to the visual studio 2005 devenv.exe.
> i then try to create a new BI project and get a message
> "Could not load file or assembly Microsoft.ReportingServices.Designer
> Version=9.0.242.0"
> another user also encountered this problem and posted it here:
> http://forums.microsoft.com/MSDN/Sho...65224&SiteID=1
> i searched for the Microsoft.ReportingServices.Designer file and it is
> located in
> C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
> i copy it to C:\Program Files\Microsoft Visual Studio
> 2005\Common7\IDE\PrivateAssemblies
> and then restart VS and lo and behold i get a different error message for
> the next file that is missing. so i try copying the entire
> PrivateAssemblies folder in to the 2005 directory and now i get "object
> reference not set to instance of an object". ok so i'm chancing my arm
> copying files around like this, but can anyone make sense of the MESS that
> is BIDS? i have repaired both my VS 2005 installation and all SQL 2005
> related software, to no avail.
> i would suspect that re-installing VS into the vs 8 directory (not the
> 2005
> directory) would solve the problem but i would hate to waste all that time
> uninstalling and reinstalling VS just because BIDS has a big installation
> bug.
> thanks for any help
> tim mackey.
>
>

Better way to use BETWEEN Begin and End Dates

/*
Subject: How best to use BETWEEN Begin and End Dates to find out if an
employee
was/is member of any group for a certain date range?

You can copy/paste this whole post in SQL Query Analyzer or Management
Studio and
run it once you've made sure there is no harmful code.

I am working on an existing database where there is code that is using
BETWEEN logic and three different OR conditions to search for a user that
has worked between begin and end date parameters that you search for.
For me the three WHERE conditions with the Begin and End dates are a little
confusing so I would like to know if there's a better/simpler way to write
this.

1- I have groups table with GroupID, Name
2- I have employees table with EmployeeID, LastName, FirstName
3- I have employeegroups table where the EmployeeID has the GroupID he/she
was/is a member of and from what Begin to what End dates.
The employee can never be a member of two groups in any date interval.
The employee always was/is a member of a group from a certain to a certain
date and then the next group he/she is a member of a group begins 1 date
after the previous group membership's end date. Therefore If I worked from
2006-01-01 to 2006-01-31 and then I changed group, well in this database
the
next group dates would begin at 2006-02-01 till an Open Ended default date
of
2009-12-31.
I can also be a member of a group for 1 day: 2006-05-05 to 2006-05-05

Please continue to read below at the bottom.

*/

USE tempdb
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'EmployeeGroups' AND xtype
= 'U')
BEGIN
TRUNCATE TABLE EmployeeGroups
DROP TABLE EmployeeGroups
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Groups' AND xtype = 'U')
BEGIN
TRUNCATE TABLE Groups
DROP TABLE Groups
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Employees' AND xtype =
'U')
BEGIN
TRUNCATE TABLE Employees
DROP TABLE Employees
END
GO

CREATE TABLE dbo.Groups
(
GroupID int NOT NULL,
Name varchar(50) NOT NULL
CONSTRAINT PK_Groups PRIMARY KEY NONCLUSTERED
(
GroupID
)
)
GO

CREATE TABLE dbo.Employees
(
EmployeeID int NOT NULL,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL
CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED
(
EmployeeID
)
)
GO

CREATE TABLE dbo.EmployeeGroups
(
EmployeeID int NOT NULL,
GroupID int NOT NULL,
BeginDate datetime NOT NULL,
EndDate datetime NOT NULL,
CONSTRAINT PK_EmployeeGroups PRIMARY KEY NONCLUSTERED
(
EmployeeID,
GroupID
),
CONSTRAINT FK_EmployeeGroups_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES Employees(EmployeeID),
CONSTRAINT FK_EmployeeGroups_Groups FOREIGN KEY
(
GroupID
) REFERENCES Groups(GroupID)
)
GO

INSERT Groups (GroupID, Name)
SELECT 1, 'Group1' UNION ALL
SELECT 2, 'Group2' UNION ALL
SELECT 3, 'Group3' UNION ALL
SELECT 4, 'Group4'
GO

INSERT Employees (EmployeeID, LastName, FirstName)
SELECT 1, 'Davolio', 'Nancy' UNION ALL
SELECT 2, 'Fuller', 'Andrew' UNION ALL
SELECT 3, 'Leverling', 'Janet' UNION ALL
SELECT 4, 'Peacock', 'Margaret' UNION ALL
SELECT 5, 'Buchanan', 'Steven'
GO

INSERT EmployeeGroups (EmployeeID, GroupID, BeginDate, EndDate)
SELECT 1, 3, '1990-01-01', '2004-10-15' UNION ALL
SELECT 1, 4, '2004-10-16', '2004-10-16' UNION ALL
SELECT 1, 1, '2004-10-17', '2099-12-31' UNION ALL
SELECT 3, 2, '1999-11-15', '2002-02-22' UNION ALL
SELECT 3, 4, '2002-02-23', '2099-12-31' UNION ALL
SELECT 4, 3, '2006-05-17', '2099-12-31'
GO

--SELECT * FROM Groups
--SELECT * FROM Employees
--SELECT * FROM EmployeeGroups

DECLARE @.EmployeeID INTEGER
DECLARE @.BeginDate DATETIME
DECLARE @.EndDate DATETIME

PRINT 'First example of querying...'
SET @.EmployeeID = 1
SET @.BeginDate = 'Sep 18 2005 12:00:00:000AM'
SET @.EndDate = 'Sep 24 2006 12:00:00:000AM'

-- This is the code logic being used in the database I am looking at.
SELECT *
FROM EmployeeGroups
WHERE EmployeeGroups.EmployeeID = @.EmployeeID
AND (
(EmployeeGroups.BeginDate <= @.BeginDate AND EmployeeGroups.EndDate

Quote:

Originally Posted by

>= @.EndDate)

OR (EmployeeGroups.BeginDate >= @.BeginDate AND
EmployeeGroups.BeginDate <= @.EndDate)
OR (EmployeeGroups.EndDate >= @.BeginDate AND EmployeeGroups.EndDate <=
@.EndDate)
)

PRINT 'Second example of querying...'
SET @.EmployeeID = 1
SET @.BeginDate = 'Sep 18 2003 12:00:00:000AM'
SET @.EndDate = 'Oct 16 2004 12:00:00:000AM'

-- This is the code logic being used in the database I am looking at.
SELECT *
FROM EmployeeGroups
WHERE EmployeeGroups.EmployeeID = @.EmployeeID
AND (
(EmployeeGroups.BeginDate <= @.BeginDate AND EmployeeGroups.EndDate

Quote:

Originally Posted by

>= @.EndDate)

OR (EmployeeGroups.BeginDate >= @.BeginDate AND
EmployeeGroups.BeginDate <= @.EndDate)
OR (EmployeeGroups.EndDate >= @.BeginDate AND EmployeeGroups.EndDate <=
@.EndDate)
)

-- For me I would swap the parameters and the EmployeeGroups.BeginDate and
-- EmployeeGroups.EndDate like this because it's easier for me to understand
the code
-- this way.
PRINT 'Third example of querying the same parameters of the second
example...'
SELECT *
FROM EmployeeGroups
WHERE EmployeeGroups.EmployeeID = @.EmployeeID
AND (
(@.BeginDate >= EmployeeGroups.BeginDate AND @.EndDate <=
EmployeeGroups.EndDate)
OR (@.BeginDate <= EmployeeGroups.BeginDate AND @.EndDate >=
EmployeeGroups.BeginDate)
OR (@.BeginDate <= EmployeeGroups.EndDate AND @.EndDate >=
EmployeeGroups.EndDate)
)

/*
I was doing some research on the newsgroup and I saw some similar examples
where Celko and David
Portas where using or discussing about using calendar tables:

http://groups.google.com/group/comp...f580af5913efcce
http://groups.google.com/group/comp...439bd3af4345ac1
First I am not sure if those examples can be applied to my scenario and also
I am not
sure how to use and whether or not I should be using a calendar table in
this case?

My question is there a better/simpler way to write these WHERE conditions or
the
whole statement?

I would appreciate any help on this.

Thank you

*/On Sun, 24 Sep 2006 12:48:20 -0400, serge wrote:

Quote:

Originally Posted by

>/*
>Subject: How best to use BETWEEN Begin and End Dates to find out if an
>employee
>was/is member of any group for a certain date range?

(snip)

Quote:

Originally Posted by

>-- This is the code logic being used in the database I am looking at.
>SELECT *
>FROM EmployeeGroups
>WHERE EmployeeGroups.EmployeeID = @.EmployeeID
AND (
(EmployeeGroups.BeginDate <= @.BeginDate AND EmployeeGroups.EndDate

Quote:

Originally Posted by

= @.EndDate)

OR (EmployeeGroups.BeginDate >= @.BeginDate AND
>EmployeeGroups.BeginDate <= @.EndDate)
OR (EmployeeGroups.EndDate >= @.BeginDate AND EmployeeGroups.EndDate <=
>@.EndDate)
)

(snip)

Quote:

Originally Posted by

>My question is there a better/simpler way to write these WHERE conditions or
>the
>whole statement?

Hi Serge,

First of all: thanks for providing CREATE TABLE and INSERT statements
with your question. That made it very easy to understand your question
and to test my reply before posting it.

Second: Yes, this can be made much simpler. I've struggled with the
"overlappping intervals" question myself once, and suddenly found the
best solution after drawing many sets of two intervals on an axis to
explore the different possibilities of overlap or non-overlap. It turned
out that there are many ways in which two intervals can overlap, but
only two ways in which they can not overlap - if interval A ends before
interval B starts, or if interval A starts after interval B ends. So the
intervals do NOT overlap IF A.end < B.start OR A.start B.end (note:
you must change < and to <= and >=, depending on your definition of
"overlap"). Reverse this to find that the condition for overlapping
intervals is simple IF A.end >= B.Start AND A.start <= B.end

Or, in your query:

SELECT *
FROM EmployeeGroups
WHERE EmployeeID = @.EmployeeID
AND BeginDate <= @.EndDate
AND EndDate >= @.BeginDate

--
Hugo Kornelis, SQL Server MVP|||Second: Yes, this can be made much simpler. I've struggled with the

Quote:

Originally Posted by

"overlappping intervals" question myself once, and suddenly found the
best solution after drawing many sets of two intervals on an axis to
explore the different possibilities of overlap or non-overlap.

Thanks Hugo. I actually drew all the possible intervals and I had
16 possibilities.

In any case your example is much easier to grasp. I'll work on
memorizing the logic so that I'm able to write this again without
reading the explanation.

Better way to check row for values?

I have a group of fields that I want to check for len() = 0. If one of
them in that group is blank len()=0 then I will set some output
parameter to yes. Then the client will report to the user that they need
to complete that section.
My solution now is to set a each field to a variable and if any one of
them are 0 (using or), set the parameter.
Is there a better way to do this?
I was thinking it would be great if there was a function that would just
return true or false if any field in that function was null/len()=0
Example
IFEmpty(Field1, Field2, etc) return True
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot comyes, use NullIf in combination with Coalesce...
NullIf(CoalA, '') returns a Null if COlA = '' (empty String), else it
returns value of ColA... So NullIf(ColA, '') will return Null if ColA IS
Null, or if ColA = '' (empty)
Then compare Coalesce of all columns together
If Coalesce(NullIf(ColA, ''), NullIf(ColB, ''), NullIf(ColC, ''),
NullIf(ColD, ''), etc, ) Is Null,
-- Then at one (or more) of them is null or empty
"Steve Lewis - Website Nation" wrote:

> I have a group of fields that I want to check for len() = 0. If one of
> them in that group is blank len()=0 then I will set some output
> parameter to yes. Then the client will report to the user that they need
> to complete that section.
> My solution now is to set a each field to a variable and if any one of
> them are 0 (using or), set the parameter.
> Is there a better way to do this?
> I was thinking it would be great if there was a function that would just
> return true or false if any field in that function was null/len()=0
> Example
> IFEmpty(Field1, Field2, etc) return True
>
> --
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>|||Cool, thanks, I will check that out. I have used NullIf before, but not
Coalesce. That looks like it will solve my problem.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/9/2005 8:49 PM, CBretana wrote:
> yes, use NullIf in combination with Coalesce...
> NullIf(CoalA, '') returns a Null if COlA = '' (empty String), else it
> returns value of ColA... So NullIf(ColA, '') will return Null if ColA IS
> Null, or if ColA = '' (empty)
> Then compare Coalesce of all columns together
> If Coalesce(NullIf(ColA, ''), NullIf(ColB, ''), NullIf(ColC, ''),
> NullIf(ColD, ''), etc, ) Is Null,
> -- Then at one (or more) of them is null or empty
> "Steve Lewis - Website Nation" wrote:
>|||I believe that your suggestion will test whether all of the fields are empty
.
Whereas the original poster was trying to test for any one of the fields
being empty.
You could try something like...
create table testtable
(
colA varchar(10),
colB varchar(10)
)
go
insert into testtable values ('', '')
insert into testtable values (null, null)
insert into testtable values ('a', '')
insert into testtable values ('a', null)
insert into testtable values (null, 'a')
insert into testtable values ('', 'a')
insert into testtable values ('a', 'a')
go
select case when substring(nullif(colA, ''), 1, 1) + substring(nullif(colb,
''), 1, 1) is null then 1 else 0 end from testtable
go
drop table testtable
"CBretana" wrote:
> yes, use NullIf in combination with Coalesce...
> NullIf(CoalA, '') returns a Null if COlA = '' (empty String), else it
> returns value of ColA... So NullIf(ColA, '') will return Null if ColA IS
> Null, or if ColA = '' (empty)
> Then compare Coalesce of all columns together
> If Coalesce(NullIf(ColA, ''), NullIf(ColB, ''), NullIf(ColC, ''),
> NullIf(ColD, ''), etc, ) Is Null,
> -- Then at one (or more) of them is null or empty
> "Steve Lewis - Website Nation" wrote:
>|||Yes, I am trying to test for any one of the fields. But, between the two
if you, I think I have an answer.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/9/2005 9:55 PM, Mike Gemmell wrote:
> I believe that your suggestion will test whether all of the fields are emp
ty.
> Whereas the original poster was trying to test for any one of the fields
> being empty.
> You could try something like...
> create table testtable
> (
> colA varchar(10),
> colB varchar(10)
> )
> go
> insert into testtable values ('', '')
> insert into testtable values (null, null)
> insert into testtable values ('a', '')
> insert into testtable values ('a', null)
> insert into testtable values (null, 'a')
> insert into testtable values ('', 'a')
> insert into testtable values ('a', 'a')
> go
> select case when substring(nullif(colA, ''), 1, 1) + substring(nullif(colb
,
> ''), 1, 1) is null then 1 else 0 end from testtable
> go
> drop table testtable
> "CBretana" wrote:
>|||Mike,
Right, Coalesce returns the first non-null parameter in the parameter list,
So only if all of them are null, will the whole Coalesce expression will be
null.
Concatenating the NullIfs, as you suggest, is the right answer...
"Mike Gemmell" wrote:
> I believe that your suggestion will test whether all of the fields are emp
ty.
> Whereas the original poster was trying to test for any one of the fields
> being empty.
> You could try something like...
> create table testtable
> (
> colA varchar(10),
> colB varchar(10)
> )
> go
> insert into testtable values ('', '')
> insert into testtable values (null, null)
> insert into testtable values ('a', '')
> insert into testtable values ('a', null)
> insert into testtable values (null, 'a')
> insert into testtable values ('', 'a')
> insert into testtable values ('a', 'a')
> go
> select case when substring(nullif(colA, ''), 1, 1) + substring(nullif(colb
,
> ''), 1, 1) is null then 1 else 0 end from testtable
> go
> drop table testtable
> "CBretana" wrote:
>|||OK , I see how the concatenation will work, but doesn't
CONCAT_NULL_YIELDS_NULL have to be set ON or is that ON by default?
According to books online:
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with
a string yields a NULL result. For example, SELECT 'abc' + NULL yields
NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null
value with a string yields the string itself (the null value is treated
as an empty string). For example, SELECT 'abc' + NULL yields abc.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/10/2005 12:03 AM, CBretana wrote:
> Mike,
> Right, Coalesce returns the first non-null parameter in the parameter lis
t,
> So only if all of them are null, will the whole Coalesce expression will
be
> null.
> Concatenating the NullIfs, as you suggest, is the right answer...
>
> "Mike Gemmell" wrote:
>|||OK , i just found that is on by default:
Option Default Setting
Set nocount OFF
Set noexec OFF
Set parseonly OFF
Set concat_null_yields_null ON
Set rowcount 0
Set ansi_defaults ON
Set arithabort ON
Set showplant_text OFF
Set statistics time OFF
Set statistics 10 OFF
Set ansi_nulls ON
Set ansi_null_dflt_on ON
Set ansi_padding ON
Set ansi_warnings ON
Set cursor_close_on_commit OFF
Set implicit_transactions OFF
Set quoted_identifier ON
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/10/2005 12:15 AM, Steve Lewis - Website Nation wrote:
> OK , I see how the concatenation will work, but doesn't
> CONCAT_NULL_YIELDS_NULL have to be set ON or is that ON by default?
> According to books online:
> When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with
> a string yields a NULL result. For example, SELECT 'abc' + NULL yields
> NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null
> value with a string yields the string itself (the null value is treated
> as an empty string). For example, SELECT 'abc' + NULL yields abc.
>
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>
> On 3/10/2005 12:03 AM, CBretana wrote:
>|||Ok , this is what I have. I will test it tomorrow:
select case when substring(nullif(colA, ''), 1, 1) +
substring(nullif(colb, ''), 1, 1) is null then set @.Incomplete = 'Yes'
else set @.Incomplete = 'No' end from tblEmployee where EmployeeID =
@.EmployeeID
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/10/2005 12:17 AM, Steve Lewis - Website Nation wrote:
> OK , i just found that is on by default:
> Option Default Setting
> Set nocount OFF
> Set noexec OFF
> Set parseonly OFF
> Set concat_null_yields_null ON
> Set rowcount 0
> Set ansi_defaults ON
> Set arithabort ON
> Set showplant_text OFF
> Set statistics time OFF
> Set statistics 10 OFF
> Set ansi_nulls ON
> Set ansi_null_dflt_on ON
> Set ansi_padding ON
> Set ansi_warnings ON
> Set cursor_close_on_commit OFF
> Set implicit_transactions OFF
> Set quoted_identifier ON
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>
> On 3/10/2005 12:15 AM, Steve Lewis - Website Nation wrote:
>|||Why don't you use CHECK constraints to validate the data on entry?
CREATE TABLE foo (... col VARCHAR(10) NOT NULL CHECK (col > '')...)
David Portas
SQL Server MVP
--

Best way to install data files?

Warning beginner here.
I posted this in the setup news group but haven't received and replies, so
perphaps someone here can help me.
I am developing a small ASP.NET web application that will use an SQL Server
2000 database. The database will start with 1,000 records in a main table
and about 100,000 records in a related table. How much this database will
grow is unknown.
I have one desktop machine that I am using for testing. It has one hard
drive, and nothing special about it.
Another test machine will pretend to be a production server. It is a real
server. It has the operating (Win Server 2003) system on a RAID 1 (2 drives)
setup. It also has 6 other drives set up as a RAID 5.
Should SQL Server be installed on the RAID 1, and then install the data
files only to the RAID 5? OR Should SQL Server and the data files be
installed on the RAID 5 drive?
The reason I ask is... From prior experience with Access I learned that
although attached data files can be worked with, they often required much
more code.
So... What is the best way to do this? And... why?
TIA.
With the disk setup that server has, install the SQL Server executables on
the RAID 1, and the user database on the RAID 5. You can create the master,
model and msdb system databases on the RAID 1 as well, and the tempdb on
RAID 1 as well, if there is enough space.
Jacco Schalkwijk
SQL Server MVP
"kvr901" <kvr901@.discussions.microsoft.com> wrote in message
news:E45F9F10-8B51-4644-A239-9A4F674BBFFD@.microsoft.com...
> Warning beginner here.
> I posted this in the setup news group but haven't received and replies, so
> perphaps someone here can help me.
> I am developing a small ASP.NET web application that will use an SQL
> Server
> 2000 database. The database will start with 1,000 records in a main table
> and about 100,000 records in a related table. How much this database will
> grow is unknown.
> I have one desktop machine that I am using for testing. It has one hard
> drive, and nothing special about it.
> Another test machine will pretend to be a production server. It is a real
> server. It has the operating (Win Server 2003) system on a RAID 1 (2
> drives)
> setup. It also has 6 other drives set up as a RAID 5.
> Should SQL Server be installed on the RAID 1, and then install the data
> files only to the RAID 5? OR Should SQL Server and the data files be
> installed on the RAID 5 drive?
> The reason I ask is... From prior experience with Access I learned that
> although attached data files can be worked with, they often required much
> more code.
> So... What is the best way to do this? And... why?
> TIA.
>
|||Thank you for your reply.
Another question:
**IF** some time in the future I decide to distribute the application
including the distributable version of SQL Server (I believe it is called the
"MSDE") would the installation procedure be the same?
In earlier versions of Access there was a considerable learning curve
(coding) to use attached Access secured "backend" databases, and I am
wondering if I should expect similar hurdles with the distributable version
of SQL Server.
Thank you.
"Jacco Schalkwijk" wrote:

> With the disk setup that server has, install the SQL Server executables on
> the RAID 1, and the user database on the RAID 5. You can create the master,
> model and msdb system databases on the RAID 1 as well, and the tempdb on
> RAID 1 as well, if there is enough space.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "kvr901" <kvr901@.discussions.microsoft.com> wrote in message
> news:E45F9F10-8B51-4644-A239-9A4F674BBFFD@.microsoft.com...
>
>
|||kvr901 wrote:[vbcol=seagreen]
> Thank you for your reply.
> Another question:
> **IF** some time in the future I decide to distribute the application
> including the distributable version of SQL Server (I believe it is called the
> "MSDE") would the installation procedure be the same?
> In earlier versions of Access there was a considerable learning curve
> (coding) to use attached Access secured "backend" databases, and I am
> wondering if I should expect similar hurdles with the distributable version
> of SQL Server.
> Thank you.
>
> "Jacco Schalkwijk" wrote:
>
I am not sure I understand your concern about attached files. Once a
database is mounted by the server accessing is no different than any
other database on the server, regardless of the location of the actual
physical data file.
As to installing your database on a customers site. The issues around
RAID are more data integrity and performance related. Whatever their
configuration is will be dependent on the number of users and their
pocket books. But the system will work on any platform that supports SQL
Server.

Best way to install data files?

Warning beginner here.
I posted this in the setup news group but haven't received and replies, so
perphaps someone here can help me.
I am developing a small ASP.NET web application that will use an SQL Server
2000 database. The database will start with 1,000 records in a main table
and about 100,000 records in a related table. How much this database will
grow is unknown.
I have one desktop machine that I am using for testing. It has one hard
drive, and nothing special about it.
Another test machine will pretend to be a production server. It is a real
server. It has the operating (Win Server 2003) system on a RAID 1 (2 drives
)
setup. It also has 6 other drives set up as a RAID 5.
Should SQL Server be installed on the RAID 1, and then install the data
files only to the RAID 5? OR Should SQL Server and the data files be
installed on the RAID 5 drive?
The reason I ask is... From prior experience with Access I learned that
although attached data files can be worked with, they often required much
more code.
So... What is the best way to do this? And... why?
TIA.With the disk setup that server has, install the SQL Server executables on
the RAID 1, and the user database on the RAID 5. You can create the master,
model and msdb system databases on the RAID 1 as well, and the tempdb on
RAID 1 as well, if there is enough space.
Jacco Schalkwijk
SQL Server MVP
"kvr901" <kvr901@.discussions.microsoft.com> wrote in message
news:E45F9F10-8B51-4644-A239-9A4F674BBFFD@.microsoft.com...
> Warning beginner here.
> I posted this in the setup news group but haven't received and replies, so
> perphaps someone here can help me.
> I am developing a small ASP.NET web application that will use an SQL
> Server
> 2000 database. The database will start with 1,000 records in a main table
> and about 100,000 records in a related table. How much this database will
> grow is unknown.
> I have one desktop machine that I am using for testing. It has one hard
> drive, and nothing special about it.
> Another test machine will pretend to be a production server. It is a real
> server. It has the operating (Win Server 2003) system on a RAID 1 (2
> drives)
> setup. It also has 6 other drives set up as a RAID 5.
> Should SQL Server be installed on the RAID 1, and then install the data
> files only to the RAID 5? OR Should SQL Server and the data files be
> installed on the RAID 5 drive?
> The reason I ask is... From prior experience with Access I learned that
> although attached data files can be worked with, they often required much
> more code.
> So... What is the best way to do this? And... why?
> TIA.
>|||Thank you for your reply.
Another question:
**IF** some time in the future I decide to distribute the application
including the distributable version of SQL Server (I believe it is called th
e
"MSDE") would the installation procedure be the same?
In earlier versions of Access there was a considerable learning curve
(coding) to use attached Access secured "backend" databases, and I am
wondering if I should expect similar hurdles with the distributable version
of SQL Server.
Thank you.
"Jacco Schalkwijk" wrote:

> With the disk setup that server has, install the SQL Server executables on
> the RAID 1, and the user database on the RAID 5. You can create the master
,
> model and msdb system databases on the RAID 1 as well, and the tempdb on
> RAID 1 as well, if there is enough space.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "kvr901" <kvr901@.discussions.microsoft.com> wrote in message
> news:E45F9F10-8B51-4644-A239-9A4F674BBFFD@.microsoft.com...
>
>|||kvr901 wrote:[vbcol=seagreen]
> Thank you for your reply.
> Another question:
> **IF** some time in the future I decide to distribute the application
> including the distributable version of SQL Server (I believe it is called
the
> "MSDE") would the installation procedure be the same?
> In earlier versions of Access there was a considerable learning curve
> (coding) to use attached Access secured "backend" databases, and I am
> wondering if I should expect similar hurdles with the distributable versio
n
> of SQL Server.
> Thank you.
>
> "Jacco Schalkwijk" wrote:
>
I am not sure I understand your concern about attached files. Once a
database is mounted by the server accessing is no different than any
other database on the server, regardless of the location of the actual
physical data file.
As to installing your database on a customers site. The issues around
RAID are more data integrity and performance related. Whatever their
configuration is will be dependent on the number of users and their
pocket books. But the system will work on any platform that supports SQL
Server.

Best way to do this

Hi Group,

I need to incoporate reporting services into my ASP.NET application.

Should I:

1. Use the ReportViewer control to map to my Server or

2. Add a reference to the web service?

Which is the 'best' option?

What are advantages, disadvantages, drawbacks of either one?

Thanks!!

Hi,

Check this link out to help you decide which one better to use.

http://www.devx.com/dotnet/Article/30424/1763.

Hth,

Michael Castillones

Best way to do a dynamic bulk insert to a table

My current project is creating a social network for the university I work for. One of the features allows members of a group to send a message to all other group members. Currently, I run a foreach loop over each of the group members, and run a separate INSERT statement to insert a message into my messages table. Once the group has several hundreds members, everybody starts getting timeout errors. What is the best way to do this?

Here are two suggestions I've received: construct one sql statement that would contain multiple INSERT statements. It would be a large statement like:

INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user, @.to_user, @.subject, @.body); INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user2, @.to_user2, @.subject2, @.body2); INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user3, @.to_user3, @.subject3, @.body3);

etc...

Or, do the foreach loop in a stored procedure. I know the pros and cons of sprocs versus dynamic sql is a sticky subject, and, personally, I'd prefer to keep my logic in the C# code-behind file. What is the best way to do this is an efficient manner? I'd be happy to share some code, if that would help. Thanks for your input!

I think what you want is something like this. 1 single query.

INSERT into [messages] (from_user, to_user, subject, body)
SELECT @.from_user,userid,@.subject,@.body FROM groupmembers WHERE groupid=@.groupid AND userid<>@.from_user

Your input parameters:

@.from_user = the sending user
@.subject = the subject
@.body = the body
@.groupid=the recieving groupid

|||

If you going to insert data to a table that alreay has some data, I suggest you to use whatgunteman wrote.

If not, I suggest you to use:

1SELECT *2INTO MyNewTable-- it will be created automatically here3FROM MyTable1 t14INNERJOIN5 MyTable2 t26ON (t1.rid = t2.rid)7

Good luck.

|||

Thanks that worked like a charm. I can't believe I never thought of putting a subquery in an insert statement.