any1 please really stuck! sql statement insert

Hi

I have the following insert statement

However it wont insert into the database

can any1 help or suggest tried everything

thanx

SqlCommand cmd2 = new SqlCommand("INSERT INTO room_type_temp (Room_code, Room_description, Room_notes, area, height, spanotes, pladescription, perdescription, floor, walls, ceiling, doorsets, glazing, windows, chanotes, hatch, air, lighting, noise, safety, awt, ast, amvs, amve, amvsu, ap, af, ah, lsi, lsn, lli, lcr, lslg, nasl, nsp, nt, sahs, AF2, sdhw, atn, amn, apn, lsin, lsnn, llin, lslgg, lcrn, nn, sn, fn, npf, nms, nin, fe, fad, Room_Sheet) SELECT Room_code, Room_description, Room_notes, area, height, spanotes, pladescription, perdescription, floor, walls, ceiling, doorsets, glazing, windows, chanotes, hatch, air, lighting, noise, safety, awt, ast, amvs, amve, amvsu, ap, af, ah, lsi, lsn, lli, lcr, lslg, nasl, nsp, nt, sahs, AF2, sdhw, atn, amn, apn, lsin, lsnn, llin, lslgg, lcrn, nn, sn, fn, npf, nms, nin, fe, fad, Room_Sheet FROM room_types where room_code = " + (Session["room_code"].ToString()), con);

Did you get error message? It seems that you missed single quote around session.

FROM room_types where room_code = " + (Session["room_code"].ToString()), con);

Should be

FROM room_types where room_code = '" + (Session["room_code"].ToString()) + "'", con);

|||

Hi

found it as well

used '" + room_code + "'

cheers!!!

:-)

Any work around for 4000 char maximum limitation in Expression

Hello,

I have to build dynamic sql statement in an SQL task.

The SQL statement is way over 4000 char.

The expression builder complains the length of the expression.

Any work around to this limitation?

Thanks a lot!

Add multiple varchar(4000) together.

DECLARE @.sql1 varchar(4000), @.sql2 varchar(4000), @.sql3 varchar(4000)

SET @.sql1 = 'stuff'
SET @.sql2 = 'more stuff'
SET @.sql3 = 'even more stuff'

EXEC (@.sql1+@.sql2+@.sql3)

|||Are you building the expression using variables and other things, or can you stick that SQL statement in a view?|||Instead of using an expression, you could build your SQL statement in a Script Task and write the result out to a file. The Execute SQL Task can read statements from a file connection. The OLE DB Source component cannot, but you could use the Execute SQL Task to put the results in a variable, then shred the results in your Data Flow using a Script Source Component.

Any work around for 4000 char maximum limitation in Expression

Hello,

I have to build dynamic sql statement in an SQL task.

The SQL statement is way over 4000 char.

The expression builder complains the length of the expression.

Any work around to this limitation?

Thanks a lot!

Add multiple varchar(4000) together.

DECLARE @.sql1 varchar(4000), @.sql2 varchar(4000), @.sql3 varchar(4000)

SET @.sql1 = 'stuff'
SET @.sql2 = 'more stuff'
SET @.sql3 = 'even more stuff'

EXEC (@.sql1+@.sql2+@.sql3)

|||Are you building the expression using variables and other things, or can you stick that SQL statement in a view?|||Instead of using an expression, you could build your SQL statement in a Script Task and write the result out to a file. The Execute SQL Task can read statements from a file connection. The OLE DB Source component cannot, but you could use the Execute SQL Task to put the results in a variable, then shred the results in your Data Flow using a Script Source Component.
sql

Any way to force a recompile on a statement within a User Defined Function?

Consider the following:
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David
|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
c2
> other
> for
> plan.
> that
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>

Any way to force a recompile on a statement within a User Defined Function?

Consider the following:
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> > Consider the following:
> >
> > TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> > There is a clustered index on (c1, c3), nonclustered index on (c2)
> >
> > The UDF contains a SELECT statement of the form:
> > select c1 from TableA where c2 = @.InputParm
> >
> > TableA contains about 300000 rows and there are some values for column
c2
> > that have a cardinality of over 30000. So obviously if this value gets
> > passed into the UDF, a Clustered Index Scan is probably best. On the
> other
> > hand if a parm gets passed for a value that has a few corresponding rows
> for
> > c2 then it is best that the nonclustered index on c2 be used.
> >
> > The problem is that when the UDF is first invoked with a parm that has a
> > high number of corresponding rows for c2, the execution plan uses a
> > clustered index SCAN and all subsuquent calls use this same execution
> plan.
> > Ideally I would like to have this UDF defined to recompile each call so
> that
> > I get the best execution plan for each call.
> >
> > If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> > (doesn't appear to be valid for functions), or I would use dynamic SQL
> > (since Functions cannot access temp tables I cannot use this approach
> > either).
> >
> > Any ideas would be appreciated.
> >
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>

Any way to force a recompile on a statement within a User Defined Function?

Consider the following:
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
c2[vbcol=seagreen]
> other
> for
> plan.
> that
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>

Any SELECT Statement Gurus Out There?

Hi all,

I'm having real trouble wrapping my newbie brain around this problem. Can
someone please tell me the most efficient (or any!) way to write a SELECT
statement to return a set of rows from 5 tables:

These tables are part of our medical database. For example, a person seeking
healthcare may want to know the names and contact information for all
doctors in Reno, NV, who do cardiology. Sounds simple, but it isn't.

A medical group can have many doctors.
A doctor may be a member of more than one medical group.
A doctor or group can have multiple specialties.
A group can have multiple facilities (physical locations).

So the tables look like this...

Table: Doctors
-----
DoctorName
DoctorID

Table: Groups
-----
GroupName
GroupID

Table: Docs2Groups (provides many-to-many relationship between Doctors and
Groups)
-------
DoctorID
GroupID

Table: Specialties (provides many-to-many relationshop between Doctors,
Groups, and Specialties)
------
SpecialtyID
SpecialtyDesc
DoctorID
GroupID

Facilities
---
FacilityName (provides one-to-many relationship between Groups and
Facilities)
FacilityID
GroupID
Address
City
State
Zip
Phone
E-mail

Any help would be GREATLY appreciated.

--Eric Robinsonthis table confuses me:

Table: Specialties (provides many-to-many relationshop between Doctors,
Groups, and Specialties)
------
SpecialtyID
SpecialtyDesc
DoctorID
GroupID

Are you saying that a doctor can only specialize in something within
the context of a certain group? In other words Dr.Smith is a pediatric
oncologist, but can only work in pediatrics for Group A and oncology
for group B? Does that happen?

It would be easier if a Group was a collection of doctors who all had
specialties.

SELECT d.DoctorName
FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
JOIN Specialties s ON d.DoctorID = s.DoctorID
JOIN Facilities f ON f.GroupID = d2g.GroupID
WHERE s.SpecialityDesc = 'cardiology'
AND f.City = 'Reno' AND f.State = 'NV'

But, if a Doctor can only specialize in something in the context of a
group, then you could do this:

SELECT d.DoctorName
FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
JOIN Specialties s ON d2g.DoctorID = s.DoctorID
AND d2g.GroupID = s.GroupID
JOIN Facilities f ON f.GroupID = d2g.GroupID
WHERE s.SpecialityDesc = 'cardiology'
AND f.City = 'Reno' AND f.State = 'NV'

Untested.

HTH,
Stu|||Stu,

Each doctor has his or her own primary specialty and 0 or more additional
specialties. These specialties are asociated with the doctor, not the group.
A group's specialty is a function of its participating doctors. If all the
docs for Group A are cardiologists, then Group A is considered a cardiology
group. If the docs are of different specialties, then the group is
considered "multispecialty." In that sense, a group is, as you say, a
collection of doctors.

HOWEVER, there are unusual cases where the group consists of docs with
different specialties, but it still wants to to be known (for the purposes
of our directory) as one certain kind of group. In these cases, the group
itself gets an "overriding" specialty associated with it to keep from being
listed as "multispecialty."

Therefore the Specialties table does double-duty, but there are only a few
records with GroupID <> 0.

Does that clear things up?

--Eric

"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126550993.851782.190410@.g43g2000cwa.googlegr oups.com...
> this table confuses me:
> Table: Specialties (provides many-to-many relationshop between Doctors,
> Groups, and Specialties)
> ------
> SpecialtyID
> SpecialtyDesc
> DoctorID
> GroupID
> Are you saying that a doctor can only specialize in something within
> the context of a certain group? In other words Dr.Smith is a pediatric
> oncologist, but can only work in pediatrics for Group A and oncology
> for group B? Does that happen?
> It would be easier if a Group was a collection of doctors who all had
> specialties.
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d.DoctorID = s.DoctorID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> But, if a Doctor can only specialize in something in the context of a
> group, then you could do this:
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d2g.DoctorID = s.DoctorID
> AND d2g.GroupID = s.GroupID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> Untested.
> HTH,
> Stu|||Oops, I mistated the Specialties table. It is actually three tables:

Specialty
---
SpecialtyID
SpecialtyDesc

Doc2Specialty
------
SpecialtyID
DoctorID

Group2Specialty
------
SpecialtyID
GroupID

"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126550993.851782.190410@.g43g2000cwa.googlegr oups.com...
> this table confuses me:
> Table: Specialties (provides many-to-many relationshop between Doctors,
> Groups, and Specialties)
> ------
> SpecialtyID
> SpecialtyDesc
> DoctorID
> GroupID
> Are you saying that a doctor can only specialize in something within
> the context of a certain group? In other words Dr.Smith is a pediatric
> oncologist, but can only work in pediatrics for Group A and oncology
> for group B? Does that happen?
> It would be easier if a Group was a collection of doctors who all had
> specialties.
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d.DoctorID = s.DoctorID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> But, if a Doctor can only specialize in something in the context of a
> group, then you could do this:
> SELECT d.DoctorName
> FROM Doctors d JOIN Docs2Groups d2g on d.DoctorID = d2g.DoctorID
> JOIN Specialties s ON d2g.DoctorID = s.DoctorID
> AND d2g.GroupID = s.GroupID
> JOIN Facilities f ON f.GroupID = d2g.GroupID
> WHERE s.SpecialityDesc = 'cardiology'
> AND f.City = 'Reno' AND f.State = 'NV'
> Untested.
> HTH,
> Stu|||Eric Robinson (eric @. pmcipa..{com}) writes:
> These tables are part of our medical database. For example, a person
> seeking healthcare may want to know the names and contact information
> for all doctors in Reno, NV, who do cardiology. Sounds simple, but it
> isn't.
> A medical group can have many doctors.
> A doctor may be a member of more than one medical group.
> A doctor or group can have multiple specialties.
> A group can have multiple facilities (physical locations).
> So the tables look like this...

It is always preferable to post CREATE TABLE scripts for the table.
That and test data in INSERT statemants, and the desired result of
the test data. That will give you a tested solution.

So this is an untested solution:

SELECT D.DoctorName
FROM Doctors D
WHERE EXISTS (SELECT *
FROM Doc2Groups DG
JOIN Facility F ON F.GroupID = DG.GroupID
WHERE DG.DoctorID = D.DoctorID
AND F.State = 'NV'
AND F.City = 'Reno')
AND EXISTS (SELECT *
FROM Doc2Specialiity DS
JOIN Specialtities S ON DS.SpecialityID = S.SpecialityID
WHERE DS.DoctorID = S.DoctorID
AND S.SpecialityDesc = 'Cardiology')
UNION
SELECT D.DoctorName
FROM Doctors D
WHERE EXISTS (SELECT *
FROM Doc2Groups DG
JOIN Facility F ON F.GroupID = DG.GroupID
JOIN Group2Specialiity GS ON GS.GroupID = DS.GroupID
JOIN Specialtities S ON GS.SpecialityID = S.SpecialityID
WHERE DG.DoctorID = D.DoctorID
AND F.State = 'NV'
AND F.City = 'Reno'
AND S.SpecialityDesc = 'Cardiology')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 12 Sep 2005 12:36:02 -0700, Eric Robinson wrote:

>Oops, I mistated the Specialties table. It is actually three tables:
(snip)

Hi Eric,

I'm glad you posted that, since I was just preparing to telll you how
you should change your design and all <grin>.

Anyway, since we now have doctors who do cardiology, groups that
specialize in cardiology and doctors that belong to groups that do
cardiology, it's not exactly clear what you want your query to return.
I'll assume you want to know what I would be interested in if my heart
starts acting funny while I'm in Reno - I'd like to know where to go
(and quick!).

The query below will return the name of the group and the details of the
location for each group with a location in Reno, NV that either has
cardiology as group specialization, or hosts at least one doctor who
specializes in cardiology.

SELECT g.GroupName, f.FacilityName, f.Address, f.Phone
FROM Groups AS g
INNER JOIN Facilities AS f
ON f.GroupID = g.GroupID
LEFT JOIN Group2Specialties AS g2s
ON g2s.GroupID = g.GroupID
CROSS JOIN (SELECT SpecialtyID
FROM Specialties
WHERE SpecialtyDesc = 'Cardiology') AS s(SpecialtyID)
WHERE f.City = 'Reno'
AND f.State = 'NV'
AND
( g2s.SpecialtyID = s.SpecialtyID
OR EXISTS
(SELECT *
FROM Docs2Groups AS d2g
INNER JOIN Doc2Specialties AS d2s
ON d2s.DoctorID = d2g.DoctorID
WHERE d2g.GroupID = g.GroupID
AND d2s.SpecialtyID = s.SpecialtyID))

(untested - see www.aspfaq.com/5006 if you prefer tested suggestions).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I was offline for a few hours and I see that I have some very throughtful
replies. I will take a closer look at them. In the meantime, based on Stu's
initial suggestion, I came up with the following, which SEEMS to work:

For simplicity's sake, I lied in my original message about the true names of
the tables, so the following query looks slightly different because it
contains real table names. (In the future I'll take Erland's suggestion and
post CREATE TABLE scripts instead.)

SELECT s.Specialty, p.LastName, p.FirstName, p.Degree, g.GroupName,
f.PhysAddr1, f.PhysCity, f.PhysAddr2, f.PhysState, f.Phone1
FROM tblProviders p JOIN tblBindProviderGroup p2g on p.ProviderID =
p2g.ProviderID
JOIN tblGroups g on g.GroupID=p2g.GroupID
JOIN tblFacilities f on f.GroupID=p2g.GroupID
JOIN tblBindProviderSpecialty p2s on p2s.ProviderID=p.ProviderID
JOIN tblSpecialties s on s.SpecialtyID=p2s.SpecialtyID
WHERE f.PhysCity='Reno'
ORDER BY p.LastName

I've run this against the actual data and it SEEMS to return correct
results.

I'm guessing that some of the other approaches people have posted are better
in ways I have not yet thought of.

--Eric

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96CFEF79E2BE1Yazorman@.127.0.0.1...
> Eric Robinson (eric @. pmcipa..{com}) writes:
>> These tables are part of our medical database. For example, a person
>> seeking healthcare may want to know the names and contact information
>> for all doctors in Reno, NV, who do cardiology. Sounds simple, but it
>> isn't.
>>
>> A medical group can have many doctors.
>> A doctor may be a member of more than one medical group.
>> A doctor or group can have multiple specialties.
>> A group can have multiple facilities (physical locations).
>>
>> So the tables look like this...
> It is always preferable to post CREATE TABLE scripts for the table.
> That and test data in INSERT statemants, and the desired result of
> the test data. That will give you a tested solution.
> So this is an untested solution:
> SELECT D.DoctorName
> FROM Doctors D
> WHERE EXISTS (SELECT *
> FROM Doc2Groups DG
> JOIN Facility F ON F.GroupID = DG.GroupID
> WHERE DG.DoctorID = D.DoctorID
> AND F.State = 'NV'
> AND F.City = 'Reno')
> AND EXISTS (SELECT *
> FROM Doc2Specialiity DS
> JOIN Specialtities S ON DS.SpecialityID = S.SpecialityID
> WHERE DS.DoctorID = S.DoctorID
> AND S.SpecialityDesc = 'Cardiology')
> UNION
> SELECT D.DoctorName
> FROM Doctors D
> WHERE EXISTS (SELECT *
> FROM Doc2Groups DG
> JOIN Facility F ON F.GroupID = DG.GroupID
> JOIN Group2Specialiity GS ON GS.GroupID = DS.GroupID
> JOIN Specialtities S ON GS.SpecialityID =
> S.SpecialityID
> WHERE DG.DoctorID = D.DoctorID
> AND F.State = 'NV'
> AND F.City = 'Reno'
> AND S.SpecialityDesc = 'Cardiology')
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Any possible optimization for the following SQL statement

Hi all,
I am relatively new to this group and forgive me if this question is
too trivial...
Say, I have an audit table AccountAudit that contains account records.
When each batch of rows are inserted, the timestamp is saved at
UpdateTime column. At a certain timestamp, there are multiple rows for
an account.
Now, given an Account# and ActionType, I'd like to get the 1st and 2rd
most recent groups of rows. Here is my SQL statement. I wonder if there
is any way to optimize it.
select * from AccountAudit where Account = 4 and ActionType = 'C'
and UpdatedTime in
(select distinct top 2 UpdatedTime from AccountAudit
where Account = 4 and ActionType = 'C'
order by UpdatedTime desc)
Thanks in advance.
FrankYou could also do this in a single SELECT instead of the nested query.
SELECT DISTINCT TOP 2 Account, ActionType, UpdatedTime
FROM Accounts
WHERE Account = 4 and ActionType = 'C'
ORDER BY UpdatedTime DESC|||To add to Lubdha's response, it is also very important to consider indexing.
A composite index on Account, ActionType, UpdatedTime may help optimize this
particular query. You'll need to consider your overall workload in order to
determine the best indexing strategy.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<fzhang@.calamos.com> wrote in message
news:1149881438.607379.14290@.i39g2000cwa.googlegroups.com...
> Hi all,
> I am relatively new to this group and forgive me if this question is
> too trivial...
> Say, I have an audit table AccountAudit that contains account records.
> When each batch of rows are inserted, the timestamp is saved at
> UpdateTime column. At a certain timestamp, there are multiple rows for
> an account.
> Now, given an Account# and ActionType, I'd like to get the 1st and 2rd
> most recent groups of rows. Here is my SQL statement. I wonder if there
> is any way to optimize it.
> select * from AccountAudit where Account = 4 and ActionType = 'C'
> and UpdatedTime in
> (select distinct top 2 UpdatedTime from AccountAudit
> where Account = 4 and ActionType = 'C'
> order by UpdatedTime desc)
>
> Thanks in advance.
> Frank
>

Any possible optimization for the following SQL statement

Hi all,
I am relatively new to this group and forgive me if this question is
too trivial...
Say, I have an audit table AccountAudit that contains account records.
When each batch of rows are inserted, the timestamp is saved at
UpdateTime column. At a certain timestamp, there are multiple rows for
an account.
Now, given an Account# and ActionType, I'd like to get the 1st and 2rd
most recent groups of rows. Here is my SQL statement. I wonder if there
is any way to optimize it.
select * from AccountAudit where Account = 4 and ActionType = 'C'
and UpdatedTime in
(select distinct top 2 UpdatedTime from AccountAudit
where Account = 4 and ActionType = 'C'
order by UpdatedTime desc)
Thanks in advance.
FrankYou could also do this in a single SELECT instead of the nested query.
SELECT DISTINCT TOP 2 Account, ActionType, UpdatedTime
FROM Accounts
WHERE Account = 4 and ActionType = 'C'
ORDER BY UpdatedTime DESC|||To add to Lubdha's response, it is also very important to consider indexing.
A composite index on Account, ActionType, UpdatedTime may help optimize this
particular query. You'll need to consider your overall workload in order to
determine the best indexing strategy.
Hope this helps.
Dan Guzman
SQL Server MVP
<fzhang@.calamos.com> wrote in message
news:1149881438.607379.14290@.i39g2000cwa.googlegroups.com...
> Hi all,
> I am relatively new to this group and forgive me if this question is
> too trivial...
> Say, I have an audit table AccountAudit that contains account records.
> When each batch of rows are inserted, the timestamp is saved at
> UpdateTime column. At a certain timestamp, there are multiple rows for
> an account.
> Now, given an Account# and ActionType, I'd like to get the 1st and 2rd
> most recent groups of rows. Here is my SQL statement. I wonder if there
> is any way to optimize it.
> select * from AccountAudit where Account = 4 and ActionType = 'C'
> and UpdatedTime in
> (select distinct top 2 UpdatedTime from AccountAudit
> where Account = 4 and ActionType = 'C'
> order by UpdatedTime desc)
>
> Thanks in advance.
> Frank
>

Any help with an update/join/count?

Hey everyone,
I'm trying to come up with an update statement for this
dataset... it's a simplified version of the data
that I'm working with, so it might not make
a lot of sense as to why it's structured as it is...
Given these tables:
create table t1 (theKey1 int,
theKey2 int,
needToPutTheDataHere varchar(1),
tooFewError int default 0,
tooManyError int default 0)
insert into t1 values(1,1,'',0,0)
insert into t1 values(2,2,'',0,0)
insert into t1 values(3,3,'',0,0)
create table t2 (theKey1 int,
theKey2 int,
theData varchar(1))
insert into t2 values(1,1,'A')
insert into t2 values(1,1,'B')
insert into t2 values(2,2,'C')
I need to update so that I get this:
t1:
thekey1 thekey2 needToPutTheDataHere too
FewError tooManyError
1 1 NULL 0 1
2 2 C 0 0
3 3 NULL 1 0
- link t1 and t2 on thekey1 and thekey2
- put t2.theData in t1.needToPutTheDataHere if there is exactly one
matching row in t2
- set t1.tooFewError or t1.tooManyError to 1 if there is less
than one or more than 1 matching row in t2.
I could also eliminate the tooFew/tooMany columns and just use a
count (0,1,...x) if that would increase performance for large datasets.
Any help on how to write this update would be much appreciated!I think that you might want to use a VIEW that will always be right
instead of updating constantly.
While I am glad to get any DDL, you have no keys and a VARCHAR(1)
column -- think about that for two seconds. Also, we do not have links
in SQL -- that is assembly language; we have references and joins.
Let's use a tally instead of assembly language style flags:
CREATE TABLE Foobar
(thekey1 INTEGER NOT NULL,
thekey2 INTEGER NOT NULL,
needtoputthedatahere CHAR(1) DEFAULT '' NOT NULL,
tally INTEGER DEFAULT 0 NOT NULL,
INSERT INTO Foobar VALUES (1, 1, '', 0, 0);
INSERT INTO Foobar VALUES (2, 2, '', 0, 0);
INSERT INTO Foobar VALUES (3, 3, '', 0, 0);
CREATE TABLE NewFoobar
(thekey1 INTEGER NOT NULL,
thekey2 INTEGER NOT NULL,
thedata CHAR(1) NOT NULL,
PRIMARY KEY (thekey1, thekey2, thedata)); -- needs a key
INSERT INTO NewFoobar VALUES (1, 1, 'a');
INSERT INTO NewFoobar VALUES (1, 1, 'b');
INSERT INTO NewFoobar VALUES (2, 2, 'c');
Try this:
UPDATE Foobar
SET needtoputthedatahere
= (SELECT F1.thedata
FROM NewFoobar AS F1
WHERE Foobar.thekey1 = F1.thekey1
AND Foobar.thekey2 = F1.thekey2
GROUP BY F1.thekey1, F1.thekey2
HAVING COUNT(*) = 1) -- empty scalar table expressions become
NULL
tally =(SELECT COUNT(*)
FROM NewFoobar AS F1
WHERE Foobar.thekey1 = F1.thekey1
AND Foobar.thekey2 = F1.thekey2) -- empty table
expressions count zero
WHERE EXISTS
(SELECT *
FROM NewFoobar
WHERE Foobar.thekey1 = NewFoobar.thekey1
AND Foobar.thekey2 = NewFoobar.thekey2);
You can drop the WHERE clause on the UPDATE if you wish.

Any equivalent of "Create or Replace"?

Hi,
Is there any equivalent in SQL server for oracle statement "Create or
Replace" such as CREATE OR REPLACE PROCEDURE......
As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
DROP and a CREATE can't reside in the same query batch file. I want to put
the whole script (for creating a new procedure or ALTERing an existing one)
in one file and be able to run it.
Any thoughts?
Thanks,
RamRam,
No, not to my knowledge. The next best way for any objects other than tables
is:
If the object exists
drop it
GO
Create the object
I do this also with tables but I have to combine them with ALTER scripts.
You don't want to create a table more than once, normally.
Ron
--
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing
one)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>|||Thanks Ron,
I found a way to do this taking cue from your advice:
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[MyStoredProc]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [MyStoredProc]
GO
CREATE PROCEDURE MyStoredProc.....
GO
In this way, I can run this query batch file and change it at any time if I
want to. This was part of a requirement to do version control on stored
procedures and now I am happy with the solution. And you are right, we won't
be doing this for tables.
Thanks,
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Ram,
> No, not to my knowledge. The next best way for any objects other than
tables
> is:
> If the object exists
> drop it
> GO
> Create the object
> I do this also with tables but I have to combine them with ALTER scripts.
> You don't want to create a table more than once, normally.
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Is there any equivalent in SQL server for oracle statement "Create or
> > Replace" such as CREATE OR REPLACE PROCEDURE......
> >
> > As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> > DROP and a CREATE can't reside in the same query batch file. I want to
put
> > the whole script (for creating a new procedure or ALTERing an existing
> one)
> > in one file and be able to run it.
> >
> > Any thoughts?
> >
> > Thanks,
> > Ram
> >
> >
>|||ALTER PROCEDURE would overwrite the existing procedure with the same name.
Actually, as the name suggestion, it would ALTER the existing Sproc.
"Ram P. Dash" wrote:
> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing one)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>
>|||Ram,
This way uses a little bit less code and doesn't access a system table:
IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
DROP PROCEDURE dbo.MyStoredProc
GO
If you have good naming conventions there's no need to use the undocumented
'P' parameter:
IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
DROP PROCEDURE dbo.usp_MyStoredProc
GO
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
> Thanks Ron,
> I found a way to do this taking cue from your advice:
> IF EXISTS (SELECT * FROM sysobjects WHERE id =OBJECT_ID(N'[MyStoredProc]')
> AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
> DROP PROCEDURE [MyStoredProc]
> GO
> CREATE PROCEDURE MyStoredProc.....
> GO
> In this way, I can run this query batch file and change it at any time if
I
> want to. This was part of a requirement to do version control on stored
> procedures and now I am happy with the solution. And you are right, we
won't
> be doing this for tables.
> Thanks,
> Ram
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> > Ram,
> >
> > No, not to my knowledge. The next best way for any objects other than
> tables
> > is:
> >
> > If the object exists
> > drop it
> > GO
> > Create the object
> >
> > I do this also with tables but I have to combine them with ALTER
scripts.
> > You don't want to create a table more than once, normally.
> >
> > Ron
> > --
> > Ron Talmage
> > SQL Server MVP
> >
> > "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> > news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > Is there any equivalent in SQL server for oracle statement "Create or
> > > Replace" such as CREATE OR REPLACE PROCEDURE......
> > >
> > > As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also
a
> > > DROP and a CREATE can't reside in the same query batch file. I want to
> put
> > > the whole script (for creating a new procedure or ALTERing an existing
> > one)
> > > in one file and be able to run it.
> > >
> > > Any thoughts?
> > >
> > > Thanks,
> > > Ram
> > >
> > >
> >
> >
>|||That's exactly what I want.
Thanks a lot Ron. You Rock!
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:ebH6Q6IuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Ram,
> This way uses a little bit less code and doesn't access a system table:
> IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
> DROP PROCEDURE dbo.MyStoredProc
> GO
> If you have good naming conventions there's no need to use the
> undocumented
> 'P' parameter:
> IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
> DROP PROCEDURE dbo.usp_MyStoredProc
> GO
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
>> Thanks Ron,
>> I found a way to do this taking cue from your advice:
>> IF EXISTS (SELECT * FROM sysobjects WHERE id => OBJECT_ID(N'[MyStoredProc]')
>> AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
>> DROP PROCEDURE [MyStoredProc]
>> GO
>> CREATE PROCEDURE MyStoredProc.....
>> GO
>> In this way, I can run this query batch file and change it at any time if
> I
>> want to. This was part of a requirement to do version control on stored
>> procedures and now I am happy with the solution. And you are right, we
> won't
>> be doing this for tables.
>> Thanks,
>> Ram
>> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
>> news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
>> > Ram,
>> >
>> > No, not to my knowledge. The next best way for any objects other than
>> tables
>> > is:
>> >
>> > If the object exists
>> > drop it
>> > GO
>> > Create the object
>> >
>> > I do this also with tables but I have to combine them with ALTER
> scripts.
>> > You don't want to create a table more than once, normally.
>> >
>> > Ron
>> > --
>> > Ron Talmage
>> > SQL Server MVP
>> >
>> > "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
>> > news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
>> > > Hi,
>> > >
>> > > Is there any equivalent in SQL server for oracle statement "Create or
>> > > Replace" such as CREATE OR REPLACE PROCEDURE......
>> > >
>> > > As far as I know, SQL Server doesn't support "CREATE OR REPLACE".
>> > > Also
> a
>> > > DROP and a CREATE can't reside in the same query batch file. I want
>> > > to
>> put
>> > > the whole script (for creating a new procedure or ALTERing an
>> > > existing
>> > one)
>> > > in one file and be able to run it.
>> > >
>> > > Any thoughts?
>> > >
>> > > Thanks,
>> > > Ram
>> > >
>> > >
>> >
>> >
>>
>

Any equivalent of "Create or Replace"?

Hi,
Is there any equivalent in SQL server for oracle statement "Create or
Replace" such as CREATE OR REPLACE PROCEDURE......
As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
DROP and a CREATE can't reside in the same query batch file. I want to put
the whole script (for creating a new procedure or ALTERing an existing one)
in one file and be able to run it.
Any thoughts?
Thanks,
Ram
Ram,
No, not to my knowledge. The next best way for any objects other than tables
is:
If the object exists
drop it
GO
Create the object
I do this also with tables but I have to combine them with ALTER scripts.
You don't want to create a table more than once, normally.
Ron
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing
one)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>
|||Thanks Ron,
I found a way to do this taking cue from your advice:
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[MyStoredProc]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [MyStoredProc]
GO
CREATE PROCEDURE MyStoredProc.....
GO
In this way, I can run this query batch file and change it at any time if I
want to. This was part of a requirement to do version control on stored
procedures and now I am happy with the solution. And you are right, we won't
be doing this for tables.
Thanks,
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Ram,
> No, not to my knowledge. The next best way for any objects other than
tables[vbcol=seagreen]
> is:
> If the object exists
> drop it
> GO
> Create the object
> I do this also with tables but I have to combine them with ALTER scripts.
> You don't want to create a table more than once, normally.
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
put
> one)
>
|||ALTER PROCEDURE would overwrite the existing procedure with the same name.
Actually, as the name suggestion, it would ALTER the existing Sproc.
"Ram P. Dash" wrote:

> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing one)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>
>
|||Ram,
This way uses a little bit less code and doesn't access a system table:
IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
DROP PROCEDURE dbo.MyStoredProc
GO
If you have good naming conventions there's no need to use the undocumented
'P' parameter:
IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
DROP PROCEDURE dbo.usp_MyStoredProc
GO
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
> Thanks Ron,
> I found a way to do this taking cue from your advice:
> IF EXISTS (SELECT * FROM sysobjects WHERE id =
OBJECT_ID(N'[MyStoredProc]')
> AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
> DROP PROCEDURE [MyStoredProc]
> GO
> CREATE PROCEDURE MyStoredProc.....
> GO
> In this way, I can run this query batch file and change it at any time if
I
> want to. This was part of a requirement to do version control on stored
> procedures and now I am happy with the solution. And you are right, we
won't[vbcol=seagreen]
> be doing this for tables.
> Thanks,
> Ram
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> tables
scripts.[vbcol=seagreen]
a
> put
>
|||That's exactly what I want.
Thanks a lot Ron. You Rock!
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:ebH6Q6IuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Ram,
> This way uses a little bit less code and doesn't access a system table:
> IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
> DROP PROCEDURE dbo.MyStoredProc
> GO
> If you have good naming conventions there's no need to use the
> undocumented
> 'P' parameter:
> IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
> DROP PROCEDURE dbo.usp_MyStoredProc
> GO
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
> OBJECT_ID(N'[MyStoredProc]')
> I
> won't
> scripts.
> a
>

Any equivalent of "Create or Replace"?

Hi,
Is there any equivalent in SQL server for oracle statement "Create or
Replace" such as CREATE OR REPLACE PROCEDURE......
As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
DROP and a CREATE can't reside in the same query batch file. I want to put
the whole script (for creating a new procedure or ALTERing an existing one)
in one file and be able to run it.
Any thoughts?
Thanks,
RamRam,
No, not to my knowledge. The next best way for any objects other than tables
is:
If the object exists
drop it
GO
Create the object
I do this also with tables but I have to combine them with ALTER scripts.
You don't want to create a table more than once, normally.
Ron
--
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing
one)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>|||Thanks Ron,
I found a way to do this taking cue from your advice:
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[MyStoredProc
]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [MyStoredProc]
GO
CREATE PROCEDURE MyStoredProc.....
GO
In this way, I can run this query batch file and change it at any time if I
want to. This was part of a requirement to do version control on stored
procedures and now I am happy with the solution. And you are right, we won't
be doing this for tables.
Thanks,
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Ram,
> No, not to my knowledge. The next best way for any objects other than
tables
> is:
> If the object exists
> drop it
> GO
> Create the object
> I do this also with tables but I have to combine them with ALTER scripts.
> You don't want to create a table more than once, normally.
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
put[vbcol=seagreen]
> one)
>|||ALTER PROCEDURE would overwrite the existing procedure with the same name.
Actually, as the name suggestion, it would ALTER the existing Sproc.
"Ram P. Dash" wrote:

> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing one
)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>
>|||Ram,
This way uses a little bit less code and doesn't access a system table:
IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
DROP PROCEDURE dbo.MyStoredProc
GO
If you have good naming conventions there's no need to use the undocumented
'P' parameter:
IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
DROP PROCEDURE dbo.usp_MyStoredProc
GO
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
> Thanks Ron,
> I found a way to do this taking cue from your advice:
> IF EXISTS (SELECT * FROM sysobjects WHERE id =
OBJECT_ID(N'[MyStoredProc]')
> AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
> DROP PROCEDURE [MyStoredProc]
> GO
> CREATE PROCEDURE MyStoredProc.....
> GO
> In this way, I can run this query batch file and change it at any time if
I
> want to. This was part of a requirement to do version control on stored
> procedures and now I am happy with the solution. And you are right, we
won't
> be doing this for tables.
> Thanks,
> Ram
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> tables
scripts.[vbcol=seagreen]
a[vbcol=seagreen]
> put
>|||That's exactly what I want.
Thanks a lot Ron. You Rock!
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:ebH6Q6IuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Ram,
> This way uses a little bit less code and doesn't access a system table:
> IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
> DROP PROCEDURE dbo.MyStoredProc
> GO
> If you have good naming conventions there's no need to use the
> undocumented
> 'P' parameter:
> IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
> DROP PROCEDURE dbo.usp_MyStoredProc
> GO
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
> OBJECT_ID(N'[MyStoredProc]')
> I
> won't
> scripts.
> a
>

Any easy way of getting a columnlist for use with an INSERT statement

Hi
I'm having some data that I need to copy from one table to another using an
INSERT statement. I'm wondering if there's an easier way to enter the column
list than having to type it in manually? In this case the table has about 20
columns (..of course with a lot of odd names...) and I find a bit
cumbersome to type all these in by hand, so hopefully some of you knows an
easier way of doing it?
Regards
SteenIn Query Analyzer, right-click on the table in the Object Browser. Mouse
over "Script Object to New Window as" and click "Insert".
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:erbYBVZmEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi
> I'm having some data that I need to copy from one table to another using
an
> INSERT statement. I'm wondering if there's an easier way to enter the
column
> list than having to type it in manually? In this case the table has about
20
> columns (..of course with a lot of odd names...) and I find a bit
> cumbersome to type all these in by hand, so hopefully some of you knows an
> easier way of doing it?
> Regards
> Steen
>|||Enjoy this :
select ' ' + T2.name + ','
from sysobjects T1, syscolumns T2
where
T1.type ='U'
and T1.Id = T2.Id
And T1.name = 'YourTable'
order by colid
Don
>--Original Message--
>Hi
>I'm having some data that I need to copy from one table
to another using an
>INSERT statement. I'm wondering if there's an easier way
to enter the column
>list than having to type it in manually? In this case the
table has about 20
>columns (..of course with a lot of odd names...) and I
find a bit
>cumbersome to type all these in by hand, so hopefully
some of you knows an
>easier way of doing it?
>Regards
>Steen
>
>.
>|||Thanks...that was just what I wanted.
Regards
Steen
David Portas wrote:
> If you are using SQL2000 you can drag the list of column names from
> the Object Browser in Query Analyzer. Just click on the word
> "columns" in the tree and drag it into the editing window.
> --
> David Portas
> SQL Server MVP

Any easy way of getting a columnlist for use with an INSERT statement

Hi
I'm having some data that I need to copy from one table to another using an
INSERT statement. I'm wondering if there's an easier way to enter the column
list than having to type it in manually? In this case the table has about 20
columns (..of course with a lot of odd names...) and I find a bit
cumbersome to type all these in by hand, so hopefully some of you knows an
easier way of doing it?
Regards
Steen
In Query Analyzer, right-click on the table in the Object Browser. Mouse
over "Script Object to New Window as" and click "Insert".
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:erbYBVZmEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi
> I'm having some data that I need to copy from one table to another using
an
> INSERT statement. I'm wondering if there's an easier way to enter the
column
> list than having to type it in manually? In this case the table has about
20
> columns (..of course with a lot of odd names...) and I find a bit
> cumbersome to type all these in by hand, so hopefully some of you knows an
> easier way of doing it?
> Regards
> Steen
>

any difference betwween these 2 statement

hi all:

who can tell me what's the difference between the following 2 statement.

The 1st one:

CREATE VIEW country_v (country_code, country_name) AS
SELECT COUNTRY.CNTRY_CD, COUNTRY.CNTRY_NM
FROM COUNTRY

The 2nd one:

CREATE VIEW country_v AS
SELECT COUNTRY.CNTRY_CD, COUNTRY.CNTRY_NM
FROM COUNTRY

Actually i didnot make sense what's usage of the parameters when creating views?

Hi,

You override the column names in the view in the first statement, in second you reuse them as they are. This is required only when a column is derived from an arithmetic expression, a function, or a constant; when two or more columns may otherwise have the same name, typically because of a join; or when a column in a view is specified a name different from that of the column from which it is derived.

Regards,

Janos

|||

It is not a parameter. It is a column aliases name. There is no big difference between these two view creation (only the column names are changed here).

Is the name to be used for a column in a view. A column name is required only when a column is derived from an arithmetic expression, a function, or a constant; when two or more columns may otherwise have the same name, typically because of a join; or when a column in a view is specified a name different from that of the column from which it is derived. Column names can also be assigned in the SELECT statement.

Note: If column is not specified, the view columns acquire the same names as the columns in the SELECT statement.

The following statements are identical.

Code Snippet

CREATE VIEW country_v (country_code, country_name)AS

SELECT COUNTRY.CNTRY_CD, COUNTRY.CNTRY_NM

FROM COUNTRY

--Or

CREATE VIEW country_vAS

SELECT COUNTRY.CNTRY_CD as country_code, COUNTRY.CNTRY_NM as country_name

FROM COUNTRY

Code Snippet

CREATE VIEW country_vAS

SELECT COUNTRY.CNTRY_CD, COUNTRY.CNTRY_NM

FROM COUNTRY

-- OR

CREATE VIEW country_v(CNTRY_CD,CNTRY_NM)AS

SELECT COUNTRY.CNTRY_CD, COUNTRY.CNTRY_NM

FROM COUNTRY

|||

Got it

Thanks to both

any difference betwween these 2 statement

hi all:

who can tell me what's the difference between the following 2 statement.

The 1st one:

CREATE VIEW country_v (country_code, country_name) AS
SELECT COUNTRY.CNTRY_CD, COUNTRY.CNTRY_NM
FROM COUNTRY

The 2nd one:

CREATE VIEW country_v AS
SELECT COUNTRY.CNTRY_CD, COUNTRY.CNTRY_NM
FROM COUNTRY

Actually i didnot make sense what's usage of the parameters when creating views?

Hi,

You override the column names in the view in the first statement, in second you reuse them as they are. This is required only when a column is derived from an arithmetic expression, a function, or a constant; when two or more columns may otherwise have the same name, typically because of a join; or when a column in a view is specified a name different from that of the column from which it is derived.

Regards,

Janos

|||

It is not a parameter. It is a column aliases name. There is no big difference between these two view creation (only the column names are changed here).

Is the name to be used for a column in a view. A column name is required only when a column is derived from an arithmetic expression, a function, or a constant; when two or more columns may otherwise have the same name, typically because of a join; or when a column in a view is specified a name different from that of the column from which it is derived. Column names can also be assigned in the SELECT statement.

Note: If column is not specified, the view columns acquire the same names as the columns in the SELECT statement.

The following statements are identical.

Code Snippet

CREATE VIEW country_v (country_code, country_name)AS

SELECT COUNTRY.CNTRY_CD, COUNTRY.CNTRY_NM

FROM COUNTRY

--Or

CREATE VIEW country_vAS

SELECT COUNTRY.CNTRY_CD as country_code, COUNTRY.CNTRY_NM as country_name

FROM COUNTRY

Code Snippet

CREATE VIEW country_vAS

SELECT COUNTRY.CNTRY_CD, COUNTRY.CNTRY_NM

FROM COUNTRY

-- OR

CREATE VIEW country_v(CNTRY_CD,CNTRY_NM)AS

SELECT COUNTRY.CNTRY_CD, COUNTRY.CNTRY_NM

FROM COUNTRY

|||

Got it

Thanks to both

Answers needed for these multiple choice questions

Question 1) Can you use the functions nvl and decode as well as the CASE statement interchangeably?

A Only NVL with DECODE and vice versa
B Only Decode with CASE statement and vice versa.
C NVL with Decode or CASE statement as well as Decode and Case Statement interchangeably
D All three can be used interchangeably

Question 2) An inner join

A Is preferred if it meets business requirements
B Requires a (+) sign in the sql syntax for Oracle
C Should never be used on dimensional metrics
D A and B
E A and C only

Question 3) You can use a Sub-select in a query after

A The FROM clause
B The IN clause
C The '=' operator
D All of the above
E All of the above and more

Question 4) Many to Many relationships:

A Result in double-counting
B May be resolved in different ways but using a relationship table is always preferred.
C Should be avoided as far as possible for simplicity
D All of the above
E A and C only

Question 5) Views:

A Often minimize re-work during the development or integration phase
B Can be used to implement security
C Can be materialized in recent versions of Oracle
D None of the above
E All of the above

ThanksWhy do you want us to GIVE you these answers? Why don't you study and do the homework yourself?|||I sure did a sincere attempt as far as homework goes also with a mixed success. For example, when I tried this on my database , I found that Question 1) could be D, Question 3) could be E and so on. And however, I may not be the strongest in logical reasoning, and hence seeking suggestions because second opinions and suggestions are valuable as they are. Atleast I will have a chance to correct myself in the chance that I may be wrong in what I thought or found. It is possible.

Thanks

ANSI_WARNINGS inconsistently in effect

I have written a VB.Net application using a SQLConnection to connect to a SQL
2000 database. The application uses an update statement in a SQLDataAdapter
to make changes to a table on the server.
The problem is that one client is getting an error with the update failing
due to string truncation. The other clients do not get an error. The only
part of the string in question that matters is the first letter, so there is
no data integrity issue.
I realize that I could put the update in a stored proc and set ANSI_WARNINGS
OFF, and then call the proc. But, I was interested in why this only is a
problem on one client.
The ANSI_WARNINGS db_option for the database is FALSE. I have searched the
MS KB and used Google Groups to search microsoft.public.* to no avail.
From what I have read, there is no way to set ANSI_WARNINGS for the
SQLConnection object. The thing that confuses me is why all clients are fine
except that one. From what I can determine, all have the same version of SQL
Server ODBC driver, etc.
Any suggestions would be appreciated.
It could be that, that particular client's table structure is different.
Check and make sure that table's column widths are the same as the ones on
the other databases.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"A. Powers" <APowers@.discussions.microsoft.com> wrote in message
news:28CE89D3-8B4A-4218-8306-1168274499B7@.microsoft.com...
I have written a VB.Net application using a SQLConnection to connect to a
SQL
2000 database. The application uses an update statement in a SQLDataAdapter
to make changes to a table on the server.
The problem is that one client is getting an error with the update failing
due to string truncation. The other clients do not get an error. The only
part of the string in question that matters is the first letter, so there is
no data integrity issue.
I realize that I could put the update in a stored proc and set ANSI_WARNINGS
OFF, and then call the proc. But, I was interested in why this only is a
problem on one client.
The ANSI_WARNINGS db_option for the database is FALSE. I have searched the
MS KB and used Google Groups to search microsoft.public.* to no avail.
From what I have read, there is no way to set ANSI_WARNINGS for the
SQLConnection object. The thing that confuses me is why all clients are
fine
except that one. From what I can determine, all have the same version of
SQL
Server ODBC driver, etc.
Any suggestions would be appreciated.