pros/cons of using Default instance and Named instance for Prod Se

Hi,
What is the pros and cons of using Default instance and Named instance for
Production Server?
I have been working with Default and I had no need for named instances.
We have a physical/vm servers for all environments.
Production always have a physical server.
So I have not needed a named instance.
What do you recommend to use in Production?
Thanks in advance.SQLapprentice wrote:
> Hi,
> What is the pros and cons of using Default instance and Named
> instance for Production Server?
> I have been working with Default and I had no need for named
> instances. We have a physical/vm servers for all environments.
> Production always have a physical server.
> So I have not needed a named instance.
> What do you recommend to use in Production?
> Thanks in advance.
I don't believe it makes any difference. You can install named instances on
the same server without additional licensing concerns (there is a limit I
believe of 8). However, you cannot use a SQL Server on a VM without a valid
license.
David Gugick
Quest Software|||I vote for a named instance. The advantage of names instances is that
they isolate the physical from the logical. Failing to do that has
caused me countless problems over the years, so I never pass up a
chance to avoid repeating that mistake.
The payback comes with configurations you knew for certain would never
change, change anyway. Example: There are problems with your
production server and have to bring up your production database on a
different box. With names instances the name can move from one box to
the other. With default, you either have to change all the apps that
point to the server, or rename the server (and then name it back when
you shift back to the original server.)
Roy
On Tue, 15 Nov 2005 10:41:06 -0800, you wrote:

>Hi,
>What is the pros and cons of using Default instance and Named instance for
>Production Server?
>I have been working with Default and I had no need for named instances.
>We have a physical/vm servers for all environments.
>Production always have a physical server.
>So I have not needed a named instance.
>What do you recommend to use in Production?
>Thanks in advance.

Proplem with SQL 2005

Hi
I have a problem with SQL 2005.

I had installed in my PC SQL Express and it with working fine with my Windows XP SP2 and VWD untill one day with I tried to lunch ASP.NET Configuration and clicked on Security , and then it showed me message " Unable to connect to SQL Server database." , i don't know what happend!!!
Here's link to screen shot about the message...
http://www.unlimitednetwork.net/data/screen_shot_1.JPG

After 2 days i Installed SQL 2005 Standerd , I said my be there's something wrong with Express version. After installed SQL Standerd still the same problem.

When i lunch Microsoft SQL Server Mangement Studio it's working fine.

And when I go to my Computer and Right Click and chose Mange it showed me that SQL 2005 working fine beside SQL 2000, but still show me message that it's not working, i don't know why!!!
Here's link to screen shot about the message...
http://www.unlimitednetwork.net/data/screen_shot_2.JPG

It's big and serious problem for me, i can't do anything, I have web site i have to finsh it soon.!!

So do you know where it can be the problem and how to solve it?!!...

another screen shot about my Computer Management to show my SQL how it's connect normaly..
http://www.unlimitednetwork.net/data/screen_shot_3.JPG

PS..
My Computer Name is AHMED
My SQL Server 2005 Name is NADA2005
My SQL Server 2000 Name is NADA or local ( it's working with both )

Thanks alot

Ahmed

Make sure that the instancename is specified in the connectionstring for the ASP application. if SQL Server Browser Server is no longer running start it (to let is redirect the application to the appropiate port, as I assume, Port 1433 is already occupied by the 2k instance) or specify the port number after the instancename with the syntax as:

ServerName\InstanceName,portnumber

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

proper time to shrink tempdb size?

hi,
working on sql server 2000.
Can you shrink tempdb's log and data file size at any time
during the day on production servers?
Will this impact other user's jobs on the server?
many thanks.
JJPlease don't post independently in multiple newsgroups. You can add the
other newsgroups in the "Newsgroups" section in the header and it will show
up in all those simultaneously as will the answers. See my reply in the
other newsgroup.
Andrew J. Kelly
SQL Server MVP
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:06b201c3a4da$e397ad60$a301280a@.phx.gbl...
> hi,
> working on sql server 2000.
> Can you shrink tempdb's log and data file size at any time
> during the day on production servers?
> Will this impact other user's jobs on the server?
> many thanks.
> JJ|||am I causing any problem this way? What's the difference
between the two ways?
Thanks.
JJ
>--Original Message--
>Please don't post independently in multiple newsgroups.
You can add the
>other newsgroups in the "Newsgroups" section in the
header and it will show
>up in all those simultaneously as will the answers. See
my reply in the
>other newsgroup.
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message
>news:06b201c3a4da$e397ad60$a301280a@.phx.gbl...
>> hi,
>> working on sql server 2000.
>> Can you shrink tempdb's log and data file size at any
time
>> during the day on production servers?
>> Will this impact other user's jobs on the server?
>> many thanks.
>> JJ
>
>.
>|||Hi JJ
There could be problems, or at least wasted time and fewer people getting
answers. If you post INDEPENDENTLY in separate newsgroups, someone could
answer you one place, and someone else could read your question in a
different group, and not know that you had already been answered. The
threads are kept completely separate., A second person could spend time
researcching and responding to a question that had already been answered.
This is wasting time they could have used to help somebody else.
If you list all the newsgroups in the header in one post, then any answer
posted in one newsgroup will be visible to people reading your question in
any of the included newsgroups.
Does that make sense?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:00f501c3a553$5bc5e3b0$a301280a@.phx.gbl...
> am I causing any problem this way? What's the difference
> between the two ways?
> Thanks.
> JJ
> >--Original Message--
> >
> >Please don't post independently in multiple newsgroups.
> You can add the
> >other newsgroups in the "Newsgroups" section in the
> header and it will show
> >up in all those simultaneously as will the answers. See
> my reply in the
> >other newsgroup.
> >
> >
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:06b201c3a4da$e397ad60$a301280a@.phx.gbl...
> >> hi,
> >>
> >> working on sql server 2000.
> >>
> >> Can you shrink tempdb's log and data file size at any
> time
> >> during the day on production servers?
> >>
> >> Will this impact other user's jobs on the server?
> >>
> >> many thanks.
> >>
> >> JJ
> >
> >
> >.
> >|||Very well put Kalen... Thank you
--
Andrew J. Kelly
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23BBKFdVpDHA.1020@.TK2MSFTNGP09.phx.gbl...
> Hi JJ
> There could be problems, or at least wasted time and fewer people getting
> answers. If you post INDEPENDENTLY in separate newsgroups, someone could
> answer you one place, and someone else could read your question in a
> different group, and not know that you had already been answered. The
> threads are kept completely separate., A second person could spend time
> researcching and responding to a question that had already been answered.
> This is wasting time they could have used to help somebody else.
> If you list all the newsgroups in the header in one post, then any answer
> posted in one newsgroup will be visible to people reading your question in
> any of the included newsgroups.
> Does that make sense?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:00f501c3a553$5bc5e3b0$a301280a@.phx.gbl...
> > am I causing any problem this way? What's the difference
> > between the two ways?
> >
> > Thanks.
> >
> > JJ
> > >--Original Message--
> > >
> > >Please don't post independently in multiple newsgroups.
> > You can add the
> > >other newsgroups in the "Newsgroups" section in the
> > header and it will show
> > >up in all those simultaneously as will the answers. See
> > my reply in the
> > >other newsgroup.
> > >
> > >
> > >
> > >--
> > >
> > >Andrew J. Kelly
> > >SQL Server MVP
> > >
> > >
> > >"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:06b201c3a4da$e397ad60$a301280a@.phx.gbl...
> > >> hi,
> > >>
> > >> working on sql server 2000.
> > >>
> > >> Can you shrink tempdb's log and data file size at any
> > time
> > >> during the day on production servers?
> > >>
> > >> Will this impact other user's jobs on the server?
> > >>
> > >> many thanks.
> > >>
> > >> JJ
> > >
> > >
> > >.
> > >
>|||good to know. thank you for the pointers. never knew
about this until now. where can I go to find out more
tips like this? I think we should make this kind of rules
very visible for people so that there won't be any more
confusions like this.
JJ
>--Original Message--
>Very well put Kalen... Thank you
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in
message
>news:%23BBKFdVpDHA.1020@.TK2MSFTNGP09.phx.gbl...
>> Hi JJ
>> There could be problems, or at least wasted time and
fewer people getting
>> answers. If you post INDEPENDENTLY in separate
newsgroups, someone could
>> answer you one place, and someone else could read your
question in a
>> different group, and not know that you had already been
answered. The
>> threads are kept completely separate., A second person
could spend time
>> researcching and responding to a question that had
already been answered.
>> This is wasting time they could have used to help
somebody else.
>> If you list all the newsgroups in the header in one
post, then any answer
>> posted in one newsgroup will be visible to people
reading your question in
>> any of the included newsgroups.
>> Does that make sense?
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote
in message
>> news:00f501c3a553$5bc5e3b0$a301280a@.phx.gbl...
>> > am I causing any problem this way? What's the
difference
>> > between the two ways?
>> >
>> > Thanks.
>> >
>> > JJ
>> > >--Original Message--
>> > >
>> > >Please don't post independently in multiple
newsgroups.
>> > You can add the
>> > >other newsgroups in the "Newsgroups" section in the
>> > header and it will show
>> > >up in all those simultaneously as will the answers.
See
>> > my reply in the
>> > >other newsgroup.
>> > >
>> > >
>> > >
>> > >--
>> > >
>> > >Andrew J. Kelly
>> > >SQL Server MVP
>> > >
>> > >
>> > >"JJ Wang" <anonymous@.discussions.microsoft.com>
wrote in
>> > message
>> > >news:06b201c3a4da$e397ad60$a301280a@.phx.gbl...
>> > >> hi,
>> > >>
>> > >> working on sql server 2000.
>> > >>
>> > >> Can you shrink tempdb's log and data file size at
any
>> > time
>> > >> during the day on production servers?
>> > >>
>> > >> Will this impact other user's jobs on the server?
>> > >>
>> > >> many thanks.
>> > >>
>> > >> JJ
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>|||today I followed your tip and posted one message with
multiple addresses to the group. One person answered me
in one group and also showed in the other, so this part
worked.
But when I answered back in one group, my answer didn't
get automatically posted in the other group. How come? I
sure hope this thing won't cause so much trouble for me.
JJ
>--Original Message--
>Very well put Kalen... Thank you
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in
message
>news:%23BBKFdVpDHA.1020@.TK2MSFTNGP09.phx.gbl...
>> Hi JJ
>> There could be problems, or at least wasted time and
fewer people getting
>> answers. If you post INDEPENDENTLY in separate
newsgroups, someone could
>> answer you one place, and someone else could read your
question in a
>> different group, and not know that you had already been
answered. The
>> threads are kept completely separate., A second person
could spend time
>> researcching and responding to a question that had
already been answered.
>> This is wasting time they could have used to help
somebody else.
>> If you list all the newsgroups in the header in one
post, then any answer
>> posted in one newsgroup will be visible to people
reading your question in
>> any of the included newsgroups.
>> Does that make sense?
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote
in message
>> news:00f501c3a553$5bc5e3b0$a301280a@.phx.gbl...
>> > am I causing any problem this way? What's the
difference
>> > between the two ways?
>> >
>> > Thanks.
>> >
>> > JJ
>> > >--Original Message--
>> > >
>> > >Please don't post independently in multiple
newsgroups.
>> > You can add the
>> > >other newsgroups in the "Newsgroups" section in the
>> > header and it will show
>> > >up in all those simultaneously as will the answers.
See
>> > my reply in the
>> > >other newsgroup.
>> > >
>> > >
>> > >
>> > >--
>> > >
>> > >Andrew J. Kelly
>> > >SQL Server MVP
>> > >
>> > >
>> > >"JJ Wang" <anonymous@.discussions.microsoft.com>
wrote in
>> > message
>> > >news:06b201c3a4da$e397ad60$a301280a@.phx.gbl...
>> > >> hi,
>> > >>
>> > >> working on sql server 2000.
>> > >>
>> > >> Can you shrink tempdb's log and data file size at
any
>> > time
>> > >> during the day on production servers?
>> > >>
>> > >> Will this impact other user's jobs on the server?
>> > >>
>> > >> many thanks.
>> > >>
>> > >> JJ
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>|||If the original post was to multiple groups and you use the Reply to Group
feature it should post to all the groups.
--
Andrew J. Kelly
SQL Server MVP
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:067b01c3aa48$46ed2620$a101280a@.phx.gbl...
> today I followed your tip and posted one message with
> multiple addresses to the group. One person answered me
> in one group and also showed in the other, so this part
> worked.
> But when I answered back in one group, my answer didn't
> get automatically posted in the other group. How come? I
> sure hope this thing won't cause so much trouble for me.
> JJ
> >--Original Message--
> >Very well put Kalen... Thank you
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Kalen Delaney" <replies@.public_newsgroups.com> wrote in
> message
> >news:%23BBKFdVpDHA.1020@.TK2MSFTNGP09.phx.gbl...
> >> Hi JJ
> >>
> >> There could be problems, or at least wasted time and
> fewer people getting
> >> answers. If you post INDEPENDENTLY in separate
> newsgroups, someone could
> >> answer you one place, and someone else could read your
> question in a
> >> different group, and not know that you had already been
> answered. The
> >> threads are kept completely separate., A second person
> could spend time
> >> researcching and responding to a question that had
> already been answered.
> >> This is wasting time they could have used to help
> somebody else.
> >>
> >> If you list all the newsgroups in the header in one
> post, then any answer
> >> posted in one newsgroup will be visible to people
> reading your question in
> >> any of the included newsgroups.
> >>
> >> Does that make sense?
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote
> in message
> >> news:00f501c3a553$5bc5e3b0$a301280a@.phx.gbl...
> >> > am I causing any problem this way? What's the
> difference
> >> > between the two ways?
> >> >
> >> > Thanks.
> >> >
> >> > JJ
> >> > >--Original Message--
> >> > >
> >> > >Please don't post independently in multiple
> newsgroups.
> >> > You can add the
> >> > >other newsgroups in the "Newsgroups" section in the
> >> > header and it will show
> >> > >up in all those simultaneously as will the answers.
> See
> >> > my reply in the
> >> > >other newsgroup.
> >> > >
> >> > >
> >> > >
> >> > >--
> >> > >
> >> > >Andrew J. Kelly
> >> > >SQL Server MVP
> >> > >
> >> > >
> >> > >"JJ Wang" <anonymous@.discussions.microsoft.com>
> wrote in
> >> > message
> >> > >news:06b201c3a4da$e397ad60$a301280a@.phx.gbl...
> >> > >> hi,
> >> > >>
> >> > >> working on sql server 2000.
> >> > >>
> >> > >> Can you shrink tempdb's log and data file size at
> any
> >> > time
> >> > >> during the day on production servers?
> >> > >>
> >> > >> Will this impact other user's jobs on the server?
> >> > >>
> >> > >> many thanks.
> >> > >>
> >> > >> JJ
> >> > >
> >> > >
> >> > >.
> >> > >
> >>
> >>
> >
> >
> >.
> >

Proper syntax for bulk insert?

Hi,

I'm working in vb.net and want to use a stored procedure to insert all employees from one db into my db. I can insert one by one, but I would like to get them all in without looping.

How would I do this? I've tried bulk insert, but I keep getting syntax errors; I've read the books online, but don't quite understand what they mean. I don't want to use DTS, should I?

Here is what I'm doing so far:

CREATE Procedure Insert_From_Personnel
@.emp_num char(10),
@.Frst_Name char(10),
@.Last_Name char(10),
@.DivisionID char (4)

as

INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

VALUES (@.emp_num,@.Frst_Name,@.Last_Name,@.DivisionID)
GO

Thanks for any help,SELECT * INTO myDB..Table FROM oneDB..Table|||Thanks Brett,

Now it's giving me an error that says 'object Individual is already in the database.'

Have I placed the select * in the right place?

CREATE Procedure Insert_From_Personnel
@.emp_num char(10),
@.Frst_Name char(10),
@.Last_Name char(10),
@.DivisionID char (4)

as

select * into individual from FROMGDPersonnelByDivision

INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

VALUES (@.emp_num,@.Frst_Name,@.Last_Name,@.DivisionID)
GO

Thanks,|||insert into db1..Individual (fields)
select fields
from db2..Individual

This will bring over all of the records, so be careful about primary/unique key constraints. Sounds like you may be doing a refresh of data for a testing database?|||Itmay take forever though. If 'select into' is enabled on db1 I'd use Brett's approach by dropping Individual from db1 and using select * into... from ...|||insert into db1..Individual (fields)
select fields
from db2..Individual

This will bring over all of the records, so be careful about primary/unique key constraints. Sounds like you may be doing a refresh of data for a testing database?

fields...oye...|||OK, OK. I am being lazy. I admit it. ;-)

Still, it is slightly better than

insert into table
select *
from other_table|||Thanks guys, I DO need all the help I can get...

MCrowley, I am creating an appliction for two users. Once a week, or whenever, I want the user to be able to import people and info from the Personnel db into the Individuals table. The field names are different.

At this point, I don't mind slow, I just want to see it work. So now I've got:

CREATE Procedure Insert_From_Personnel

as

INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
select emp_num,frst_name,last_name,[division id]
from FROMGDPersonnelByDivision

GO

I wiped out everything to start from scratch. I ran the SP and got the 'string or binary data would be truncated' error. Ran it again and got the 'violation of PK_Individual' error. There isn't any data in the table yet, how can it be violated?

Just to see if I've got it right, I took the PK off and it worked! It was pretty speedy, too. I'll try to write something on the vb.net side to Update if it sees the same emp_num and Insert if that emp_num doesn't exist.

One last thing... how can I say if it DOESN'T exist INSERT in the SP?

Thanks for your help!|||if not exists (select...)|||I'm getting closer, I hope. I've tried the if exists all over the place and weeded it down to only one 'incorrect syntax' near from FROMGDPersonnelByDivision

What the heck am I missing?!

CREATE Procedure Insert_From_Personnel
as
if not exists (select emp_num,frst_name,last_name,[division id])

INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

from FROMGDPersonnelByDivision
GO

Can I have one more hint, please?|||Yeah, I agree, the combination is finite and you're close ;)

CREATE Procedure Insert_From_Personnel
as
if not exists (select 1 from FROMGDPersonnelByDivision where <something> = <something>) INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
else
print 'Record already exists'
return (0)
GO|||rdjabarov,

Help me out here. Why isn't this working?

When I use your example (simplified):
CREATE Procedure Insert_From_Personnel

as

if not exists (select emp_num,frst_name, last_name, [division id] from FROMGDPersonnelByDivision where emp_num = emp_num)
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

GO

It gives me the 'incorrect syntax near ')' after the last DivisionID, as if it needs something more.

After looking around on the web and in my Murach book, I tried putting values back in and using 'default values'. But then it has problems with the 'as'. As if it does not want VALUES using the 'if not exists'. What does it need? I'm not finding much info about combining 'if not' with 'insert into'.

Thanks for any help!|||The INSERT INTO requires either a VALUES clause or a SELECT statement to provide the data that you want inserted.

-PatP|||Hmmmm... OK,

The select is there. It makes sense in english;

if this doesn't exist: (the data from these columns, from THAT table) then INSERT it into THIS table

>> (select emp_num,frst_name, last_name, [division id] from >>FROMGDPersonnelByDivision where emp_num = emp_num)

Will I have better luck using 'WHERE NOT EXISTS'? Is there a difference?

Thanks,|||How about:INSERT into individual (
IndividualID, FirstName, LastName
, DivisionID
) SELECT
emp_num, frst_name, last_name
, [division id]
FROM FROMGDPersonnelByDivision
WHERE NOT EXSISTS (SELECT *
FROM individual AS b
WHERE b.InidividualID = FROMGDPersonnelByDivision.emp_num)-PatP|||Pat! Pat! It worked!

I was just trying to manipulate the code you gave for the 'Create Trigger?' post. I knew that the a.xxx and b.xxx sounded like where I should go. I didn't know I could just use a 'b'.

Thank you SO much! I'll be using this all over the place at work, so it's very important I get this concept down.|||Well, I am glad someone has a better eye sight than me ;)

Proper Application of a Subquery

In order to expand my skills in SQL for a database I am working, I developed
a conceptual query with which I am having some difficulty. The problem can
be represented as three tables: tblApartments, tblResidents, tblPhone.
tblResidents links to tblApartments through a field called AptNum. Current,
past, and future residents are stored in tblResidents and catagorized by a
field called Status. Phone numbers (e.g. home, emergency, work, etc) are
linked to the appropriate resident via a field called ResID. tblApartment
contains details about the apartment like building number and floor.
Now let's say I want to get a list of names and work phone numbers for
people that lived in building #3. The query I came up with to get the names
looks like:
SELECT tblApartments.Building, tblApartments.AptNum,
tblResidents.Name, tblResidents.AptNum, tblResidents.Status
FROM tblApartments INNER JOIN tblResidents ON
tblApartments.AptNum = tblResidents.AptNum
WHERE tblApartments.Building = 3 AND
tblResidents.Status = "Moved"
What I can't get a handle on is how to add work phone numbers. I am
assuming a subquery is the correct construct and would be of the form
SELECT tblResidents.ResID, tblPhone.ResID, tblPhone.Number, tblPhone.Type
FROM tblResidents INNER JOIN tblPhone ON
tblResidents.ResID = tblPhone.ResID
WHERE tblPhone.Type = "Work"
However, I don't have a clue how to integrate this into the overall query!
Am I barking up the wrong tree with this structure? If not, any help or
references to online FAQs or tutorials would be greatly appreciated. (I did
look at some on-line references, but it did not help!)
Any help will be greatly appreciated!!
Thanks!
DonDon wrote:
> In order to expand my skills in SQL for a database I am working, I develop
ed
> a conceptual query with which I am having some difficulty. The problem ca
n
> be represented as three tables: tblApartments, tblResidents, tblPhone.
> tblResidents links to tblApartments through a field called AptNum. Curren
t,
> past, and future residents are stored in tblResidents and catagorized by a
> field called Status. Phone numbers (e.g. home, emergency, work, etc) are
> linked to the appropriate resident via a field called ResID. tblApartment
> contains details about the apartment like building number and floor.
> Now let's say I want to get a list of names and work phone numbers for
> people that lived in building #3. The query I came up with to get the nam
es
> looks like:
> SELECT tblApartments.Building, tblApartments.AptNum,
> tblResidents.Name, tblResidents.AptNum, tblResidents.Stat
us
> FROM tblApartments INNER JOIN tblResidents ON
> tblApartments.AptNum = tblResidents.AptNum
> WHERE tblApartments.Building = 3 AND
> tblResidents.Status = "Moved"
> What I can't get a handle on is how to add work phone numbers. I am
> assuming a subquery is the correct construct and would be of the form
> SELECT tblResidents.ResID, tblPhone.ResID, tblPhone.Number, tblPhone.Type
> FROM tblResidents INNER JOIN tblPhone ON
> tblResidents.ResID = tblPhone.ResID
> WHERE tblPhone.Type = "Work"
> However, I don't have a clue how to integrate this into the overall query!
> Am I barking up the wrong tree with this structure? If not, any help or
> references to online FAQs or tutorials would be greatly appreciated. (I d
id
> look at some on-line references, but it did not help!)
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Just include a join of the tblPhone to it's related table:
SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
FROM (tblApartments As A
INNER JOIN tblResidents As R
ON A.AptNum = R.AptNum)
LEFT JOIN tblPhone As P
ON R.ResID = P.ResID
WHERE A.Building = 3
AND R.Status = "Moved"
AND P.Type = "Work"
I used a LEFT JOIN on the tblPhone in case a resident did not have a
Work phone number.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQh+Xr4echKqOuFEgEQJT3QCfRSFLOB36+5+e
0gJlQbDM46GtRGcAn05A
CBHjNQ63P/0GQ76Yy82VTWT/
=B/s0
--END PGP SIGNATURE--|||"Don" <someone@.somewhere.net> wrote in message
news:OdOgs73GFHA.3440@.TK2MSFTNGP10.phx.gbl...
> In order to expand my skills in SQL for a database I am working, I
developed
> a conceptual query with which I am having some difficulty. The problem
can
> be represented as three tables: tblApartments, tblResidents, tblPhone.
> tblResidents links to tblApartments through a field called AptNum.
Current,
> past, and future residents are stored in tblResidents and catagorized by a
> field called Status. Phone numbers (e.g. home, emergency, work, etc) are
> linked to the appropriate resident via a field called ResID. tblApartment
> contains details about the apartment like building number and floor.
> Now let's say I want to get a list of names and work phone numbers for
> people that lived in building #3. The query I came up with to get the
names
> looks like:
> SELECT tblApartments.Building, tblApartments.AptNum,
> tblResidents.Name, tblResidents.AptNum,
tblResidents.Status
> FROM tblApartments INNER JOIN tblResidents ON
> tblApartments.AptNum = tblResidents.AptNum
> WHERE tblApartments.Building = 3 AND
> tblResidents.Status = "Moved"
> What I can't get a handle on is how to add work phone numbers. I am
> assuming a subquery is the correct construct and would be of the form
> SELECT tblResidents.ResID, tblPhone.ResID, tblPhone.Number, tblPhone.Type
> FROM tblResidents INNER JOIN tblPhone ON
> tblResidents.ResID = tblPhone.ResID
> WHERE tblPhone.Type = "Work"
> However, I don't have a clue how to integrate this into the overall query!
> Am I barking up the wrong tree with this structure? If not, any help or
> references to online FAQs or tutorials would be greatly appreciated. (I
did
> look at some on-line references, but it did not help!)
> Any help will be greatly appreciated!!
> Thanks!
> Don
>
I don't think a subquery is required for this, another INNER JOIN should do
it:
SELECT tblApartments.Building, tblApartments.AptNum, tblResidents.Name,
tblResidents.AptNum, tblResidents.Status, tblPhone.Number, tblPhone.Type
FROM tblApartments
INNER JOIN tblResidents ON tblApartments.AptNum = tblResidents.AptNum
INNER JOIN tblPhone ON tblResidents.ResID = tblPhone.ResID
WHERE tblApartments.Building = 3 AND tblResidents.Status = "Moved" AND
tblPhone.Type = "Work"
It's a matter of taste, but I use joins in preference to subqueries where
possible because I think they are easier to read. Also, I've read that the
Query Optimiser often handles joins better than subqueries.
Regards,
Simon|||On Fri, 25 Feb 2005 21:24:59 GMT, MGFoster wrote:
(snip)
>Just include a join of the tblPhone to it's related table:
>SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
>FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum)
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID
>WHERE A.Building = 3
> AND R.Status = "Moved"
> AND P.Type = "Work"
>I used a LEFT JOIN on the tblPhone in case a resident did not have a
>Work phone number.
Hi MGFoster,
In order for that to work, the P.Type = 'Work' shoould be in the ON
clause, not in the WHERE clause. (And you shouldn't use double quotes to
delimit string constants!)
SELECT A.Building, A.AptNum,
R.Name, R.AptNum, R.Status,
P.Number, P.Type
FROM Apartments AS A
INNER JOIN Residents AS R
ON R.AptNum = A.AptNum
LEFT JOIN Phones AS P
ON P.ResID = R.ResID
AND P.Type = 'Work'
WHERE A.Building = 3
AND R.Status = 'Moved'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"MGFoster" <me@.privacy.com> wrote in message
news:LIMTd.5610$MY6.682@.newsread1.news.pas.earthlink.net...
> SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
> FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum)
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID
> WHERE A.Building = 3
> AND R.Status = "Moved"
> AND P.Type = "Work"
> I used a LEFT JOIN on the tblPhone in case a resident did not have a
> Work phone number.
I believe that if you want to see all residents even if they have no work
phone that you need to add the P.type condition to the Join clause as:
SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
FROM (tblApartments As A
INNER JOIN tblResidents As R
ON A.AptNum = R.AptNum)
LEFT JOIN tblPhone As P
ON R.ResID = P.ResID and P.Type = "Work"
WHERE A.Building = 3
AND R.Status = "Moved"
Good Luck,
Jim|||James Goodwin wrote:
> "MGFoster" <me@.privacy.com> wrote in message
> news:LIMTd.5610$MY6.682@.newsread1.news.pas.earthlink.net...
>
>
> I believe that if you want to see all residents even if they have no work
> phone that you need to add the P.type condition to the Join clause as:
> SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status
> FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum)
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID and P.Type = "Work"
> WHERE A.Building = 3
> AND R.Status = "Moved"
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Hugo & Jim,
Double quotes: Yeah, I know. I move between Access (double quotes OK)
& SQL a lot & probably just got discombobulated.
P.Type = 'Work' in ON clause: I've seen the equivalency evaluation of
"hard coded" data in the JOIN's ON clause before, but I've always put,
what could be a parameter, in the WHERE clause. Is there any increased
efficiency in putting it in the ON clause rather than the WHERE clause?
If there is an efficiency increase, wouldn't that indicate that all
WHERE clause evaluations could be put into the join's ON clause?
I'm not advocating this just curious. Also, I'm too lazy to create some
test tables & data to look at the query's execution plan. ;-)
Thanks,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQh/ s1IechKqOuFEgEQJK3QCg+2KBLrUaTtXxbQ98L95
US9slC6IAn3rb
5PXNZ5Plgzrt65L864qdd7rX
=xIwu
--END PGP SIGNATURE--|||"MGFoster" <me@.privacy.com> wrote in message
news:j1STd.5822$MY6.1200@.newsread1.news.pas.earthlink.net...
> James Goodwin wrote:
work
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Hugo & Jim,
> Double quotes: Yeah, I know. I move between Access (double quotes OK)
> & SQL a lot & probably just got discombobulated.
> P.Type = 'Work' in ON clause: I've seen the equivalency evaluation of
> "hard coded" data in the JOIN's ON clause before, but I've always put,
> what could be a parameter, in the WHERE clause. Is there any increased
> efficiency in putting it in the ON clause rather than the WHERE clause?
> If there is an efficiency increase, wouldn't that indicate that all
> WHERE clause evaluations could be put into the join's ON clause?
> I'm not advocating this just curious. Also, I'm too lazy to create some
> test tables & data to look at the query's execution plan. ;-)
> Thanks,
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/AwUBQh/ s1IechKqOuFEgEQJK3QCg+2KBLrUaTtXxbQ98L95
US9slC6IAn3rb
> 5PXNZ5Plgzrt65L864qdd7rX
> =xIwu
> --END PGP SIGNATURE--
Hello -
In this case, LEFT OUTER JOIN tblPhone ON (R.ResID = P.ResID and P.Type =
'Work') is necessary to ensure that people who don't have work phones are
returned. If you put the P.Type = 'Work' condition in the overall WHERE
clause, then people who don't have work phones will not be returned, because
their P.Type value is null.
Alternatively, you could use the simpler JOIN condition make the condition
in the WHERE clause (P.Type = 'Work' OR P.Type IS NULL).
Regards,
Simon|||> "MGFoster" <me@.privacy.com> wrote in message
< snip >
< snip >
Simon Shearn wrote:
> Hello -
> In this case, LEFT OUTER JOIN tblPhone ON (R.ResID = P.ResID and P.Type =
> 'Work') is necessary to ensure that people who don't have work phones are
> returned. If you put the P.Type = 'Work' condition in the overall WHERE
> clause, then people who don't have work phones will not be returned, becau
se
> their P.Type value is null.
> Alternatively, you could use the simpler JOIN condition make the condition
> in the WHERE clause (P.Type = 'Work' OR P.Type IS NULL).
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Simon, Thanks for the info. It made me want to see how this works. So
I went ahead & created some test tables/data:
use tempdb
go
set nocount on
create table tblApartments (
building tinyint,
aptnum tinyint primary key
)
insert into tblApartments values (1,1)
insert into tblApartments values (1,2)
insert into tblApartments values (1,3)
insert into tblApartments values (2,4)
insert into tblApartments values (2,5)
insert into tblApartments values (3,6)
insert into tblApartments values (3,7)
insert into tblApartments values (3,8)
create table tblResidents(
ResID tinyint primary key,
[name] varchar(20),
aptnum tinyint ,
status varchar(10),
constraint fk_res foreign key (aptnum) references tblApartments
)
insert into tblResidents values (1,'dalton',1,'moved')
insert into tblResidents values (2,'liloo',2,'current')
insert into tblResidents values (3,'lucy',3,'moved')
insert into tblResidents values (4,'harry',4,'moved')
insert into tblResidents values (5,'sondine',5,'current')
insert into tblResidents values (6,'jean-baptiste',6,'moved')
insert into tblResidents values (7,'betty',7,'moved')
insert into tblResidents values (8,'cornelius',8,'current')
create table tblPhone (
ResID tinyint ,
Type varchar(5),
[Number] varchar(10),
constraint fk_phone foreign key (resid) references tblResidents
)
insert into tblPhone values (1,'work','055-1234')
insert into tblPhone values(2,'home','155-1234')
--insert into tblPhone values(3,'work','255-1234')--
insert into tblPhone values(3,'home','355-1234')
insert into tblPhone values(5,'work','455-1234')
insert into tblPhone values(6,'work','555-1234')
insert into tblPhone values(7,'home','655-1234')
--insert into tblPhone values(7,'work','755-1234')-- uncomment to get
betty's PH#
SELECT A.Building, A.AptNum, R.[Name], P.[Number] as Phone, R.Status
FROM (tblApartments As A
INNER JOIN tblResidents As R
ON A.AptNum = R.AptNum ) -- and r.status = 'moved')
LEFT JOIN tblPhone As P
ON R.ResID = P.ResID AND P.Type = 'Work'
WHERE A.Building = 3
AND R.Status = 'Moved'
-- AND (P.Type = 'Work') -- OR P.Type IS NULL)
drop table tblPhone
drop table tblResidents
drop table tblApartments
set nocount off
Result set:
Building AptNum Name Phone Status
-- -- -- -- --
3 6 jean-baptiste 555-1234 moved
3 7 betty NULL moved
Which is correct, 'cuz betty & jean-baptiste are the only residents of
building 3 apts who have moved.
============
If you change the FROM & WHERE clause to this:
FROM (tblApartments As A
INNER JOIN tblResidents As R
ON A.AptNum = R.AptNum )
LEFT JOIN tblPhone As P
ON R.ResID = P.ResID --AND P.Type = 'Work'
WHERE A.Building = 3
AND R.Status = 'Moved'
AND (P.Type = 'Work' OR P.Type IS NULL)
The result set is:
Building AptNum Name Phone Status
-- -- -- -- --
3 6 jean-baptiste 555-1234 moved
which means the "OR P.Type IS NULL" criteria doesn't pull betty's record
as you suggested it would.
This solves some problems I've had w/ LEFT JOINS not working as I had
anticipated. Thanks for the info.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQiEODIechKqOuFEgEQLr0ACgylrysm6ilcay
4mfQ1n/B5GcLw3oAoJ+9
DDimxetXGCEb6UEz2vEmaA4m
=oICZ
--END PGP SIGNATURE--|||"MGFoster" <me@.privacy.com> wrote in message
news:K68Ud.6547$873.1771@.newsread3.news.pas.earthlink.net...
> < snip >
> < snip >
> Simon Shearn wrote:
=
are
because
condition
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Simon, Thanks for the info. It made me want to see how this works. So
> I went ahead & created some test tables/data:
> use tempdb
> go
> set nocount on
> create table tblApartments (
> building tinyint,
> aptnum tinyint primary key
> )
> insert into tblApartments values (1,1)
> insert into tblApartments values (1,2)
> insert into tblApartments values (1,3)
> insert into tblApartments values (2,4)
> insert into tblApartments values (2,5)
> insert into tblApartments values (3,6)
> insert into tblApartments values (3,7)
> insert into tblApartments values (3,8)
> create table tblResidents(
> ResID tinyint primary key,
> [name] varchar(20),
> aptnum tinyint ,
> status varchar(10),
> constraint fk_res foreign key (aptnum) references tblApartments
> )
> insert into tblResidents values (1,'dalton',1,'moved')
> insert into tblResidents values (2,'liloo',2,'current')
> insert into tblResidents values (3,'lucy',3,'moved')
> insert into tblResidents values (4,'harry',4,'moved')
> insert into tblResidents values (5,'sondine',5,'current')
> insert into tblResidents values (6,'jean-baptiste',6,'moved')
> insert into tblResidents values (7,'betty',7,'moved')
> insert into tblResidents values (8,'cornelius',8,'current')
> create table tblPhone (
> ResID tinyint ,
> Type varchar(5),
> [Number] varchar(10),
> constraint fk_phone foreign key (resid) references tblResidents
> )
> insert into tblPhone values (1,'work','055-1234')
> insert into tblPhone values(2,'home','155-1234')
> --insert into tblPhone values(3,'work','255-1234')--
> insert into tblPhone values(3,'home','355-1234')
> insert into tblPhone values(5,'work','455-1234')
> insert into tblPhone values(6,'work','555-1234')
> insert into tblPhone values(7,'home','655-1234')
> --insert into tblPhone values(7,'work','755-1234')-- uncomment to get
> betty's PH#
> SELECT A.Building, A.AptNum, R.[Name], P.[Number] as Phone, R.Status
> FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum ) -- and r.status = 'moved')
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID AND P.Type = 'Work'
> WHERE A.Building = 3
> AND R.Status = 'Moved'
> -- AND (P.Type = 'Work') -- OR P.Type IS NULL)
> drop table tblPhone
> drop table tblResidents
> drop table tblApartments
> set nocount off
> Result set:
> Building AptNum Name Phone Status
> -- -- -- -- --
> 3 6 jean-baptiste 555-1234 moved
> 3 7 betty NULL moved
> Which is correct, 'cuz betty & jean-baptiste are the only residents of
> building 3 apts who have moved.
> ============
> If you change the FROM & WHERE clause to this:
> FROM (tblApartments As A
> INNER JOIN tblResidents As R
> ON A.AptNum = R.AptNum )
> LEFT JOIN tblPhone As P
> ON R.ResID = P.ResID --AND P.Type = 'Work'
> WHERE A.Building = 3
> AND R.Status = 'Moved'
> AND (P.Type = 'Work' OR P.Type IS NULL)
> The result set is:
> Building AptNum Name Phone Status
> -- -- -- -- --
> 3 6 jean-baptiste 555-1234 moved
> which means the "OR P.Type IS NULL" criteria doesn't pull betty's record
> as you suggested it would.
> This solves some problems I've had w/ LEFT JOINS not working as I had
> anticipated. Thanks for the info.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQiEODIechKqOuFEgEQLr0ACgylrysm6ilcay
4mfQ1n/B5GcLw3oAoJ+9
> DDimxetXGCEb6UEz2vEmaA4m
> =oICZ
> --END PGP SIGNATURE--
Hello -
Yes, you're right - the alternative form I suggested only works when the
person involved has no phone of any kind, so putting the P.Type = 'Work' in
the join condition, as others suggested, is the correct way of doing it.
Regards,
Simon|||On Sat, 26 Feb 2005 03:28:15 GMT, MGFoster wrote:
(snip)
>P.Type = 'Work' in ON clause: I've seen the equivalency evaluation of
>"hard coded" data in the JOIN's ON clause before, but I've always put,
>what could be a parameter, in the WHERE clause. Is there any increased
>efficiency in putting it in the ON clause rather than the WHERE clause?
Hi MGFoster,
Sorry for the late reply. The flu managed to get me down; I'm now
struggling to remove as much as possible from my 400+ message backlog
before my headache forces me back to bed again. :-)
Anyway, Simon already pointed out that in the case of outer joins, the
choice to put things in the WHERE clause or the ON clause influences the
results.
In the case of INNER joins, there is no performance difference, so
choose what suits you best. My preference (and I know I'm not alone with
this) is to code the "proper" joining criteria (usually following the
defined foreign keys) in the ON and the filter criteria in the WHERE.
But that's just my personal preference.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Prompt box with Crystal

I am currently working on billing reports. I wish to add a facility that will allow us to use Crystal as a facility to query the SQL database. Ideally a prompt box of some sort would ask for a Customer ID of which will then filter the report content down to that customer. This could be achieved by directly intervieing with the SQL query, but this wouldn't suit with our staff.

I also have some other posts up if you are willing to help.

Thanks in advance.Ok, after doing a little research and reading the manual I think I may have found something that can assist me in this problem.

Parameter's.

I have set up a parameter that asks for a customer id, but it doesn't return the report or refresh the content of the report specific to that customer. Im unsure of how to approach this.

Regards

Promelm with SQL 2005

Hi
I have a problem with SQL 2005.

I had installed in my PC SQL Express and it with working fine with my Windows XP SP2 and VWD untill one day with I tried to lunch ASP.NET Configuration and clicked on Security , and then it showed me message " Unable to connect to SQL Server database." , i don't know what happend!!!
Here's link to screen shot about the message...
http://www.unlimitednetwork.net/data/screen_shot_1.JPG

After 2 days i Installed SQL 2005 Standerd , I said my be there's something wrong with Express version. After installed SQL Standerd still the same problem.

When i lunch Microsoft SQL Server Mangement Studio it's working fine.

And when I go to my Computer and Right Click and chose Mange it showed me that SQL 2005 working fine beside SQL 2000, but still show me message that it's not working, i don't know why!!!
Here's link to screen shot about the message...
http://www.unlimitednetwork.net/data/screen_shot_2.JPG

It's big and serious problem for me, i can't do anything, I have web site i have to finsh it soon.!!

So do you know where it can be the problem and how to solve it?!!...

another screen shot about my Computer Management to show my SQL how it's connect normaly..
http://www.unlimitednetwork.net/data/screen_shot_3.JPG

PS..
My Computer Name is AHMED
My SQL Server 2005 Name is NADA2005
My SQL Server 2000 Name is NADA or local ( it's working with both )

Thanks alot

Ahmed

Make sure that the instancename is specified in the connectionstring for the ASP application. if SQL Server Browser Server is no longer running start it (to let is redirect the application to the appropiate port, as I assume, Port 1433 is already occupied by the 2k instance) or specify the port number after the instancename with the syntax as:

ServerName\InstanceName,portnumber

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

Progress Versus SQL Server

Progress is a piece of crap! Sure it runs on Unix. But the time
invested in learning and working with it obscene! With SQL Server I
could easily plan a maintenance backup plan and transaction backup plan
- Progress - well you have to learn about AI files, sizing the BI and
AI files as well as the data files. It's a freakin nightmare. If I
want to see data in a table, can I simply click on a table and open it
- NO! Instead, I have to learn the 4GL language to write a script to
return records (what if I want to resort those records - write another
4GL script and run it again). But hey, why do you want to see the data
in the tables (at least that's what I was told by my company) Progress
GUI - what a joke. This database server might have been popular with
unix but the world today is GUI and Microsoft are the GUI experts.
Even with the GUI tools in Progress, I still can't click on a table,
open it and see the records. Maybe that's not important to others but
as a dba, it sure is a necessity. We're working with different
versions of Progress and none of them integrate with the other. I've
spent 2 months now trying to run some simple database maintenance on
Progress and all I can say is that I hate BI, AI, and D1, D2, D3 files.
Give me SQL Server any day where at least I can manage databases in a
normal logical way without having to deal with a stinkin .ST files and
prostrct this...prostrct that!!! Any Progress responses are welcome
but I'll take you on any day against SQL Server. If you're a
Pro-Progress and anti-SQL Server, you're behind the times - get with
the newest technology and GUI (not a GUI in development.) I have yet
to see a true relational database designed in Progress. Upsizing a
Progress database into a GUI environment - HA!I worked with Progress for several years running mission critical
applications and all I can say is that is an excellent technology as good as
SQL Server or Oracle.
Ben Nevarez, MCDBA, OCP
Database Administrator
"pkohn@.charter.net" wrote:

> Progress is a piece of crap! Sure it runs on Unix. But the time
> invested in learning and working with it obscene! With SQL Server I
> could easily plan a maintenance backup plan and transaction backup plan
> - Progress - well you have to learn about AI files, sizing the BI and
> AI files as well as the data files. It's a freakin nightmare. If I
> want to see data in a table, can I simply click on a table and open it
> - NO! Instead, I have to learn the 4GL language to write a script to
> return records (what if I want to resort those records - write another
> 4GL script and run it again). But hey, why do you want to see the data
> in the tables (at least that's what I was told by my company) Progress
> GUI - what a joke. This database server might have been popular with
> unix but the world today is GUI and Microsoft are the GUI experts.
> Even with the GUI tools in Progress, I still can't click on a table,
> open it and see the records. Maybe that's not important to others but
> as a dba, it sure is a necessity. We're working with different
> versions of Progress and none of them integrate with the other. I've
> spent 2 months now trying to run some simple database maintenance on
> Progress and all I can say is that I hate BI, AI, and D1, D2, D3 files.
> Give me SQL Server any day where at least I can manage databases in a
> normal logical way without having to deal with a stinkin .ST files and
> prostrct this...prostrct that!!! Any Progress responses are welcome
> but I'll take you on any day against SQL Server. If you're a
> Pro-Progress and anti-SQL Server, you're behind the times - get with
> the newest technology and GUI (not a GUI in development.) I have yet
> to see a true relational database designed in Progress. Upsizing a
> Progress database into a GUI environment - HA!
>|||pkohn@.charter.net wrote:
> Progress is a piece of crap! Sure it runs on Unix. But the time
> invested in learning and working with it obscene! With SQL Server I
> could easily plan a maintenance backup plan and transaction backup plan
> - Progress - well you have to learn about AI files, sizing the BI and
> AI files as well as the data files. It's a freakin nightmare. If I
> want to see data in a table, can I simply click on a table and open it
> - NO! Instead, I have to learn the 4GL language to write a script to
> return records (what if I want to resort those records - write another
> 4GL script and run it again). But hey, why do you want to see the data
> in the tables (at least that's what I was told by my company) Progress
> GUI - what a joke. This database server might have been popular with
> unix but the world today is GUI and Microsoft are the GUI experts.
> Even with the GUI tools in Progress, I still can't click on a table,
> open it and see the records. Maybe that's not important to others but
> as a dba, it sure is a necessity. We're working with different
> versions of Progress and none of them integrate with the other. I've
> spent 2 months now trying to run some simple database maintenance on
> Progress and all I can say is that I hate BI, AI, and D1, D2, D3 files.
> Give me SQL Server any day where at least I can manage databases in a
> normal logical way without having to deal with a stinkin .ST files and
> prostrct this...prostrct that!!! Any Progress responses are welcome
> but I'll take you on any day against SQL Server. If you're a
> Pro-Progress and anti-SQL Server, you're behind the times - get with
> the newest technology and GUI (not a GUI in development.) I have yet
> to see a true relational database designed in Progress. Upsizing a
> Progress database into a GUI environment - HA!
>
Tossing in my 2-cents... I'm not a Progress guy, never touched it. I
do however find it interesting that your entire argument seems to be
based on SQL offering a better GUI. I blame that GUI for some of the
basic, everyday questions that cross this newsgroup on a daily basis.
My database has been running for 2 years, and my transaction log is
250GB, why? How can I export a database to a different server? The GUI
that you seem to cherish allows any Access user to think he's a SQL
Server DBA, without forcing him/her to learn the most basic tasks.
Point-and-click through the maintenance plan wizard, without
understanding a thing about what it's actually doing, all is well until
that plan begins to fail. Since they never understood what it was
doing, they have no idea where to start troubleshooting. Oh, look,
there's a "New Database Wizard", I can create a database! What's my
backup strategy? Do I need transactional recovery capabilities, or are
daily full backups enough? Dunno, the wizard didn't ask me those questions.
Personally, the only time I touch Enterprise Manager is when I have to
work with some DTS package that somebody dragged objects onto,
connecting them with workflow lines, because that's the only way they
know how to execute a stored procedure.

Progress Database ODBC Source

Hello,

I need to pull in data from a progress database into a table in SQL server 2005. I have an ODBC conncetion on the server where I am working.

I am new to SSIS but have used DTS. I need to succsefully pull the data into a table in SQL server 2005 from this progress database.

I have setup my ODBC connection but I cannot pull the data using this connection. The connection test succeeded for the connection manager. When I attempt to use the datasource reader and point to the ODBC Connection Manager entry I recieve Cannot acquire a managed connection from the run time connection manager

Any help is appreciated.

Thanks,

Tony

Hi Tony,

you need to configure your connection as ADO.NET for ODBC. DataReader source can only work with ADO.NET connections.

|||Try to create connection using .NET Provider\ODBC Data Provider I have tried it at my end it's working fine.

Programming in ssis

Hi..

I am working on sql server integration services..New to this topic...I want to transfer data from flat file to sql database by doing it programatically.Creating source,destination and transform adapters

my basic question is should the source,transform and destination adapter be bulid for each file i am trying to transfer.

What is the advantage of this doing it this way.

Would anyone let me know about this..

Thanks

The answer to your question will depend on your application's requirements, but in principle, if you are using the same metadata throughout the data flow, you can use a for-each loop and dynamically update the connection string of the flat file connection manager, using the property expression feature.

Programmatically set a Flat File Connection Manager

Hello,

I need to know how I can programmatically set a Flat File Connection Manager's Column Delimiter value.

The Data Warehouse project I am working on, receives daily information feeds that could contain one of two delimiters. Which is just dumb...anyways, as it is now we have two seperate Data Flow Tasks which handle these two delimiters. Currently we have a script taks that "sneak previews" each incoming flat file to determine which delimiter it has, and direct our flow to the correct Data Flow Task to handle it.

I do not want to have to maintain 2 DFTs. How can I get around this problem?

Even if there is a way to do this by passing variables/setting expressions in the Flat File Connection manager, I would do that. Does not necessarily HAVE to be a pure programmatic approach.

ANY help would be greatly appreciated!

Feel free to email me at ccorbin@.topcoder.com with any questions, or leave me some good news here :)

THANKS!

Chris Corbin

Software Developer

TopCoder Inc.

I thought this was a simple question. Apparently, you can use an expression to set every property of the File Connection Manager except the column delimiter. Maybe that will change in the near future. There must be a reason.|||ColumnDelimiter is a property of the FlatFileColumn, and not the connection manager. Due to which currently it is not expressionable. Feel free to raise a DCR(Design Change Request) for this issue.|||

How would I go about raising a DCR?

Thanks

|||

To file a bug/DCR, start here:

http://connect.microsoft.com/SQLServer

Programmatically adding a user to the System User role

We have been working on an application that will be using a forms-authenticated report server (RS2005) as a reporting back-end. Using the reporting services web service I have been able to assign permissions to objects in reporting services no problem. The issue is that each user needs to be added to the System User role to be able to use the report builder properly. I can't seem to find a way to do this programmatically. Any idea?Nevermind, GetSystemPolicies() is what I was looking for.

Programmatically Accessing an SQLDataSource with a "SELECT COUNT(*)" query.

I've found example code of accessing an SQLDataSource and even have it working in my own code - an example would be

Dim datastuff As DataView = CType(srcSoftwareSelected.Select(DataSourceSelectArguments.Empty), DataView)

Dim row As DataRow = datastuff.Table.Rows(0)
Dim installtype As Integer = row("InstallMethod")
Dim install As String = row("Install").ToString
Dim notes As String = row("Notes").ToString

The above only works on a single row, of course. If I needed more, I know I can loop it.

The query in srcSoftwareSelected is something like "SELECT InstallMethod, Install, Notes FROM Software"

My problem lies in trying to access the data in a simliar way when I'm using a SELECT COUNT query.

Dim datastuff As DataView = CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), DataView)
Dim row As DataRow = datastuff.Table.Rows(0)
Dim count As Integer = row("rowcnt")

The query here is "SELECT COUNT(*) as rowcnt FROM Software"

The variable count is 1 every time I query this, no matter what the actual count is. I know I've got to be accessing the incorrect data member in the 2nd query because a gridview tied to srcSoftwareUsage (the SQLDataSource) always displays the correct value.

Where am I going wrong here?

The following should work.

Dim datastuffAs System.Data.DataView =CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), System.Data.DataView)

Dim drAs System.Data.DataRow = datastuff .Table.Rows(0)

Dim mycountAsString = Convert.ToInt32(dr("rowcnt")).ToString()

'Label1.Text = mycount

|||

Hi there,

Aren't you getting the row count from your SELECT COUNT query (1 row obviously)? Instead of getting the result value from that query?

gonzzas

|||

It's very similar to what I've tested out, but that exact code will show that mycount = "1" instead of the actual value.

What is interesting is the GridView control I set up on the test page is outputting the correct result.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="srcSoftwareUsage" Width="527px">
<Columns>
<asp:BoundField DataField="rowcnt" HeaderText="rowcnt" ReadOnly="True" SortExpression="rowcnt" />
</Columns>
</asp:GridView>

Now, it's obviously accessing the column named rowcnt. I can debug my code and manually look at the column values in DataView.Table.Rows(0) and it shows the value 1 and nothing more.

|||

Yes, I can use either code to get the correct count from my query. What is your SqlDataSource code?

Here is what I tested:

Dim dvAs System.Data.DataView =CType(SqlDataSource2.Select(DataSourceSelectArguments.Empty), System.Data.DataView)

Dim rowAs System.Data.DataRow = dv.Table.Rows(0)

' For Each row As System.Data.DataRow In dv.Table.Rows

Dim mycountAsString = Convert.ToInt32(row("rowcnt")).ToString()

Label2.Text = mycount

' Next

|||

Murphy's Law probably applies as I didn't give you thecomplete story - I naively thought this part shouldn't matter as it'sthe table output is identical.

It appears to be my sql query.

I'm not actually looking for the count of rows in the Software table, but I'm looking for the count of times a particular row in Software is referenced by 2 other tables - RoleSoft and TeamSoft

With my simple query above, both the code and GridView worked. With this one - the GridView works, the code doesn't.

SELECT COUNT(*) AS rowcnt FROM (SELECT Role, Software FROM RoleSoft WHERE (Software = @.Id) UNION ALL SELECT Team, Software FROM TeamSoft WHERE (Software = @.Id)) AS derivedtbl_1

I thought it was a moot point as the table output appears identical from each query. Obviously I'm wrong. I'm imagining the derivedtbl_1 is probably where I'm getting bogus data in the code.

1> SELECT COUNT(*) AS rowcnt FROM (SELECT Role, Software FROM RoleSoft WHERE (Software = 2) UNION ALL SELECT Team, Software FROM TeamSoft WHERE (Software = 2))
AS derivedtbl_1
2> go
rowcnt
----
3

(1 rows affected)
1> SELECT COUNT(*) as rowcnt FROM Software
2> go
rowcnt
----
8

(1 rows affected)

|||

Bah. I figured it out. It wasn't even the SQL statement. I had updated the @.Id parameter in the srcSoftwareUsage_Selecting event handler and I misused a global variable. It kept setting @.Id to 1 and the count for that Id was always 1.

Now I feel stupid for wasting your time and mine on this. Thanks for the help, though.

Programatically Verifying SQL Syntax

OK, so I'm working on a project that requires the dynamic generation of SQL statements. Now in Visual Studio .NET I'm sure many of you have used that wonderful visual database tool that allows you to magically "verify sql syntax" at the click of a button. It takes a query that looks like this:

"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.EMail, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductId, P.BuyerId FROM Affiliates A, Purchases P WHERE (ProductId > 0) ORDER BY 1"

and transforms it into something that looks like this:

"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.Email, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductID, P.BuyerID
FROM Affiliates A CROSS JOIN Purchases P
WHERE (P.ProductID > 0)
ORDER BY A.AFFID"

My question is: is there anything in the .NET platform that would allow me to achieve the same kind of result programatically (i.e. transform standard sql to tsql syntax and verify the syntax of the query at run time)?

Any help would be appreciated! Cheers.No, you have to sit down and start programming.

Having a parser generator available helps, if you find a SQL syntax as LALR.|||I was afraid you would say that.

Programatically query database and save report in an excel table

Hello All,

I am working on constructing a software layer around some features of the RDL language that would allow me to programatically generate reports.

I am reading the RDL specification language, and I do not understand three things:

1) How the DataSet element is populated by the query or more precisely how do the <Field> elements capture all the rows inside the table that is being queries?

To my understanding I wilkl have to define several fields that correspond to all columns of interest in the query.

But that is only for one row (?!) How do the rest of the rows get populated? Does the server recursively create new rows based on my definitions until it matches row for row all the data in the table?

2) Once the elements are inside a DataSet how do make use of that data to render it in a table.

I understand how the DataSource, DataSet, and Table work individually, yet I do not understand how to establish a flow of data between DataSet and Table.

3) Do I even need to use a <Table> as an RDL element in order to organize the data in an excel table?

I would appreciate any help. Thank you!

Anyone?