Marking a subscription for reinitialization

Is there a command I can execute through SQL Query Analyzer that will mark a
subscription for reinitialization?
Thanks
Brad
Never mind...found my answer in BOL.
Brad
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:%23ve4jolJEHA.2576@.TK2MSFTNGP12.phx.gbl...
> Is there a command I can execute through SQL Query Analyzer that will mark
a
> subscription for reinitialization?
> Thanks
> Brad
>

marked transactions

I am running the following script in Query Analizer:
begin transaction xxxx with mark
update logmarks set logmark=2
commit transaction xxxx
go
The script runs without error and the logmarks table is updated correctly,
but when I check the logmarkhistory table to see if a row was inserted there
is nothing in there. What am I doing wrong or why does this happen?
Thanks in advance for any help.The transaction information will be stored in the logmarkhistory table
only if there is a active log backup chain.sql

Mapping Package Variables to a SQL Query in an OLEDB Source Component

Learning how to use SSIS...

I have a data flow that uses an OLEDB Source Component to read data from a table. The data access mode is SQL Command. The SQL Command is:

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare)

I wanted to add additional clauses to the where clause.

The problem is that I want to add to this SQL Command the ability to have it use a package variable that at the time of the package execution uses the variable value.

The package variable is called [User::Date_BeginningYesterday]

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]

I have looked at various forum message and been through the BOL but seem to missing something to make this work properly.

http://msdn2.microsoft.com/en-us/library/ms139904.aspx

The article, is the closest I have (what I belive) come to finding a solution. I am sure the solution is so easy that it is staring me in the face and I just don't see it. Thank you for your assistance.

...cordell...

Not sure what your problem is; but the solution is to create a variable to hold your query, let's say [User::SQLStatement] and use as value your query. Then set EvaluateAsExpression property of the variable to true. In the expression property, create an expression that will be evaluate at run time where you concatenate your query with the [User::Date_BeginningYesterday] variable. Back in your OLE DB Component you need to choose 'SQL Statement from variable' and then choose [User::SQLStatement] from the list.

Notice that you need to cast the value of [User::Date_BeginningYesterday] to string in the expression builder before concatenating its value to the sql statement.

Rafael Salas

select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]

|||

The question that I have is: Can one embed a package variable into a sql statement while selecting "SQL Statement" from the data access mode. If so how would would go about that?

...cordell...

|||

The short answer is no. you cannot reference a SSIS variable directly in your sql statement. You need to use '?' and then use the parameter mapping in your OLE DB source OR, to concatenate it within a second varibale as I described in the previous post.

Rafael Salas

sql

Many-to-many SQL Query (Failed to enable constraints)

Hi,

I have two tables (Accounts and Contacts) that have a many-to-many relationship, maintained by the AccountContactLinks table.

I would like to populate a Contacts DropDownList with all of the Contacts associated with the Account Selected in Accounts DropDownList.

Here is the (SP) SQL Query I'm trying to make work:

SELECT Contact.ContactID,
Contact.ContactLastName,
Contact.ContactFirstName,
Contact.ContactLastName+', '+ Contact.ContactFirstNameAS ContactName
FROM ContactINNERJOIN AccountContactLinksON Contact.ContactID= AccountContactLinks.ContactID
WHERE(AccountContactLinks.AccountID= @.AccountID)
ORDERBY Contact.ContactLastName
END

I keep getting the following error:

Failed to enable constraints. One or more rows contains values violating non-null, unique or foreign key constraints.

I haven't implemented any non-null, unique or foreign key constraints between any of these tables, so suspect that I've got the SQL Query wrong.

Thanks very much.

Regards

Gary

You must be trying to read that into a dataset that is set up wrong. If you are just trying to fill a dropdown, then put a dropdown control on the page, put a sqldatasource control on the page, put the SQL you have there (Or the name of the SP) as the query string, and set up the @.AccountID parameter.|||

Thanks, Motley - what you suggest is exactly what I'm doing. I've tried it both a SQL query (which runs fine in the Query Editor) and as an SP.

It's just when I test the Query in the SqlDataSource Wizard and when I try to run the page that it doesn't work.

Do you have any other ideas?

Thanks very much.

Regards

Gary

|||

It looks good to me... Well, except that you have an END statement, but I'm assuming that you cut and pasted from your SP which had a BEGIN before the select statement.

Are you sure there are no foreign key constraints? Sure looks like the AccountContactsLinks and Contacts table would be a good candidate for one.

|||

Thanks, Motley - I've found out what's wrong (by creating a new set of tables to ensure a clean start and making the same mistake, inadvertently).

The mistake that I made (twice) was to create identical records, which in a table without a primary key, introduces duplicates (i.e. creates non-unique records). I thought that I would have had to specify that records had to be unique, but now realise that, if the linking table doesn't have a key field, there is no way of identifying one record from another if the two fields are the same.

I'm concerned that SQL Server allowed this to happen and unless you or someone else has ways of preventing it from happening again, I'm going to re-introduce a Primary Key field (which someone else suggested I remove) as a safeguard.

So, I'll have to rebuild my linking table. Fortunately I've been playing around with a test database, so it won't take too long. I'll then conclude this thread with a confirmation, for the sake of people with the same problem in future.

Regards

Gary

|||I'm glad you found it. I always recommend that all tables have a primary key -- even if it's one that you create yourself via a uniqueidentifier or an identity field. In some rare cases, like in your link table, where there really is no "new" information, but just linking 2 different objects IDs to show a relationship, I'll put both fields into the primary key.

many-to-many help

I have a many-to-many query that is giving me false positives.
Lets say I have some entitiesTeam Members and Teams. Team Members are onTeams, and they could be in one or many Teams. They are represented in tables thusly:
Team Member:
MemberID
TeamMemberName

Team:
TeamID
TeamName

They are associated in this table:
Association:
TeamID
MemberID

I have teams of the following makeup:
Team1:
John
Mary

Team2:
John
Mary
Susan

Team3:
John
Billy

Lets say I have no idea what the teams are, and someone hands me the names John and Mary and tells me I have to find out the single TeamName associated with these people, and only these people. The query I have constructed so far will give me a false positive on Team2.Hi pinkbear and welcome to TSDN !

can you post the query you have as a start point

Regards Purplesql

Many to One with Max Date Query?

I have a master table that has all my accounts in it.
In a 2nd table I have update notes per each account, so there are
multiple notes entries for each single ID account in teh first table.

How can I get the OLDEST dated entry in teh second table, for every
account that's had a not entered into if by joining on the ID of the
first table?

This has been driving me nuts, does that make sense to you?

Here:

Table 1 FIELDS
ACCOUNTID

TABLE 2 FIELDS
NOTEID
ACCOUNTID (Foreign Key)
DATE_ENTERED

I need to join those two tables on the ACCOUNTID, but ONLY show the most
recent date for the record I pull from Table 2, since it's got multiple
entries in table 2, it's screwing my query up. Any help/ideas?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!SELECT T1.accountid, T2.noteid, T2.date_entered
FROM Table1 AS T1
JOIN Table2 AS T2
ON T1.accountid = T2.accountid
AND T2.date_entered =
(SELECT MIN(date_entered)
FROM Table2
WHERE accountid = T2.accountid)

--
David Portas
SQL Server MVP
--

Many to one relationship

Hello

I have need to write a query that I can pass in a bunch of filter criteria, and return 1 result...it's just ALL of the criteria must be matched and a row returned:

example:

Transaction table: id, reference

attribute table: attributeid, attribute

transactionAttribute: attributeid, transactionid

Example dat

Attribute table contains: 1 Red, 2 Blue, 3 Green

Transaction table contains: 1 one, 2 two, 3 three

transactionAttribute contains: (1,1), (1,2), (1,3), (2,3), (3,1)

If I pass in Red, Blue, Green - I need to be returned "one" only

If I pass in Red - I need to be returned "three" only

If I pass in Red, Green - nothing should be returned as it doesn't EXACTLY match the filter criteria

If anyone's able to help that would be wonderful!

Thanks, Paul

Hi Paul,

To get the result, you cannot pass the Attribute strings directly, you have to seperate them with commas. Each one is quoted with single quotes.

Here is an example for

SELECT * FROM TransactionAttribute
LEFT OUTER JOIN AttributeTable ON TransactionAttribute.AttributeId = AttributeTable.AttributeId
LEFT OUTER JOIN TransactionTable ON TransactionAttribute.TransactionId=TransactionTable.TransactionID
WHERE Attribute IN ('Red', 'Green')

HTH.

sql

Many to Many to Many SQL Query

I have 3 data tables, A, B and C, with many to many relationship
tables between A-B and A-C.

The data in A and C changes rarely, and the A-C relationship relates
all possible combinations of A to a C

If A contains A.1 to A.3 and C contains C.1 - C.8 then A-C could
contain the records:

A.1, C.1
A.2, C.2
A.3, C.3
A.1, C.4
A.2, C.4
A.1, C.5
A.3, C.5
A.2, C.6
A.3, C.6
A.1, C.7
A.2, C.7
A.3, C.7

so that any set of records from A (including the empty set) relates to
exactly on record in C

and suppose that B contains records from B.1 to B.3, and A-B contains
records
A.2, B.1
A.1, B.2
A.3, B.2

What I am having touble doing is crafting a query that will take me
from a record in B to the record in C that has the corrisponding set
of records in A-C as is in A-B for the chosen B.

ie, I want a query that will give me a result set something like

B.1, C.2
B.2, C.5
B.3, C.8

As far as I can come up with, this is not doable in a single query,
but perhaps I am missing something...

Simon WithersPlease post DDL (CREATE TABLE statements) for these tables - simplified if
possible but including keys and constraints. Also post some sample data as
INSERT statements. It will save you a lot of typing and make it easier for
others to understand your problem.

--
David Portas
----
Please reply only to the newsgroup
--|||Create/Insert scripts:

CREATE TABLE [Product] (
[ProductID] [int] NOT NULL ,
[Name] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Project] (
[ProjectID] [int] NOT NULL ,
[Name] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED
(
[ProjectID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [ProjectProduct] (
[ProjectID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
CONSTRAINT [PK_ProjectProduct] PRIMARY KEY CLUSTERED
(
[ProjectID],
[ProductID]
) ON [PRIMARY] ,
CONSTRAINT [FK_ProjectProduct_Product] FOREIGN KEY
(
[ProductID]
) REFERENCES [Product] (
[ProductID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_ProjectProduct_Project] FOREIGN KEY
(
[ProjectID]
) REFERENCES [Project] (
[ProjectID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO

CREATE TABLE [SubledgerID] (
[SubledgerID] [int] NOT NULL ,
[Name] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
CONSTRAINT [PK_SubledgerID] PRIMARY KEY CLUSTERED
(
[SubledgerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [SubledgerProduct] (
[SubledgerID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
CONSTRAINT [PK_SubledgerProduct] PRIMARY KEY CLUSTERED
(
[SubledgerID],
[ProductID]
) ON [PRIMARY] ,
CONSTRAINT [FK_SubledgerProduct_Product] FOREIGN KEY
(
[ProductID]
) REFERENCES [Product] (
[ProductID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_SubledgerProduct_SubledgerID] FOREIGN KEY
(
[SubledgerID]
) REFERENCES [SubledgerID] (
[SubledgerID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO

INSERT INTO [Product] ([ProductID], [Name]) VALUES (1, 'Product A')
INSERT INTO [Product] ([ProductID], [Name]) VALUES (2, 'Product B')
INSERT INTO [Product] ([ProductID], [Name]) VALUES (3, 'Product C')

INSERT INTO [Project] ([ProjectID], [Name]) VALUES (1, 'Project 1')
INSERT INTO [Project] ([ProjectID], [Name]) VALUES (2, 'Project 2')
INSERT INTO [Project] ([ProjectID], [Name]) VALUES (3, 'Project 3')

INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (1, 'Subl a')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (2, 'Subl b')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (3, 'Subl c')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (4, 'Subl d')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (5, 'Subl e')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (6, 'Subl f')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (7, 'Subl g')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (8, 'Subl h')

INSERT INTO [ProjectProduct] ([ProjectID], [ProductID]) VALUES (1, 3)
INSERT INTO [ProjectProduct] ([ProjectID], [ProductID]) VALUES (2, 1)
INSERT INTO [ProjectProduct] ([ProjectID], [ProductID]) VALUES (2, 3)

INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (1,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (2,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (3,
3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (4,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (4,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (5,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (5,
3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (6,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (6,
3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (7,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (7,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (7,
3)

Desired Output:

SELECT Project.Name, Project.ProjectID, SubledgerID.Name,
SubledgerID.SubledgerID FROM ???

Producing:

Project 1, 1, Subl c, 3
Project 2, 2, Subl f, 6
Project 3, 3, Subl h, 8

Simon Withers

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||try this script

SELECT Project.Name, Project.ProjectID, SubledgerID.Name,
SubledgerID.SubledgerID FROM Project , SubledgerID , ProjectProduct,
SubledgerProduct WHERE SubledgerProduct.ProductID IN
( SELECT ProjectProduct.ProductID FROM ProjectProduct WHERE
ProjectProduct.ProjectID = Project.ProjectID )
AND SubledgerID.SubledgerID=SubledgerProduct.Subledger ID GROUP BY
Project.Name, Project.ProjectID, SubledgerID.Name,
SubledgerID.SubledgerID

it will produce

Project 1 1 Subl c 3
Project 1 1 Subl e 5
Project 1 1 Subl f 6
Project 1 1 Subl g 7
Project 2 2 Subl a 1
Project 2 2 Subl c 3
Project 2 2 Subl d 4
Project 2 2 Subl e 5
Project 2 2 Subl f 6
Project 2 2 Subl g 7

is it ok?

--
..bucho
ICQ#: 38153136
Distributed.net RC5-72
ID: support@.1580.ru|||I'm not sure I've understood your requirement correctly, but does this help:

SELECT DISTINCT PR.name, PR.projectid, SL.name, SL.subledgerid
FROM Project AS PR
JOIN ProjectProduct AS PP
ON PP.projectid = PR.projectid
JOIN SubLedgerProduct AS SP
ON SP.productid = PP.productid
JOIN SubLedgerID AS SL
ON SL.subledgerid = SP.subledgerid

--
David Portas
----
Please reply only to the newsgroup
--|||[posted and mailed, please reply in news]

What a devilish problem! Took me quite some time understand what you
are looking for!

What I am offering is only a partial solution, and may even prove to be
a non-solution. It looks like this:

SELECT a.SubledgerID, b.ProjectID
FROM (SELECT s.SubledgerID, sumid = SUM(coalesce(sp.ProductID, 0)),
cnt = COUNT(sp.ProductID)
FROM SubledgerID s
LEFT JOIN SubledgerProduct sp ON s.SubledgerID = sp.SubledgerID
GROUP BY s.SubledgerID) AS a
JOIN (SELECT p.ProjectID, sumid = SUM(coalesce(pp.ProductID, 0)),
cnt = COUNT(pp.ProductID)
FROM Project p
LEFT JOIN ProjectProduct pp ON pp.ProjectID = p.ProjectID
GROUP BY p.ProjectID) AS b
ON a.sumid = b.sumid
AND a.cnt = b.cnt

It works that you get the same signature on ProductID no matter you
come from Subledger or you come from Project.

Now, the trouble here is finding a signature which does not give any false
positive. I know that there a similar problem, and I offered a suggestion
that Steve Kass proved not be safe. Unfortunately, I don't remember if
we ever found something that was waterproof. This time I didn't even
try, but I kind of hope that Steve who is a mathematician jumps in.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Simon,

This is my solution. I used your first example (because of its shorter
names!)
It's funny that we don't need table A at all. All we need is its
relations to B and C.

select
b,
c = (select C.c
from C left outer join AC on (C.c = AC.c and
a in (select a
from AB
where b = B.b)
)
group by C.c
having count(a) = (select count(*) from AB where b = B.b)
and count(a) = (select count(*) from AC where AC.c = C.c)
)
from B

Good Luck,
Shervin

simon@.klickit.com (Simon Withers) wrote in message news:<78088784.0309220828.62c349e4@.posting.google.com>...
> I have 3 data tables, A, B and C, with many to many relationship
> tables between A-B and A-C.
> The data in A and C changes rarely, and the A-C relationship relates
> all possible combinations of A to a C
> If A contains A.1 to A.3 and C contains C.1 - C.8 then A-C could
> contain the records:
> A.1, C.1
> A.2, C.2
> A.3, C.3
> A.1, C.4
> A.2, C.4
> A.1, C.5
> A.3, C.5
> A.2, C.6
> A.3, C.6
> A.1, C.7
> A.2, C.7
> A.3, C.7
> so that any set of records from A (including the empty set) relates to
> exactly on record in C
> and suppose that B contains records from B.1 to B.3, and A-B contains
> records
> A.2, B.1
> A.1, B.2
> A.3, B.2
> What I am having touble doing is crafting a query that will take me
> from a record in B to the record in C that has the corrisponding set
> of records in A-C as is in A-B for the chosen B.
> ie, I want a query that will give me a result set something like
> B.1, C.2
> B.2, C.5
> B.3, C.8
> As far as I can come up with, this is not doable in a single query,
> but perhaps I am missing something...
> Simon Withers|||Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> This is my solution. I used your first example (because of its shorter
> names!)
> It's funny that we don't need table A at all. All we need is its
> relations to B and C.

Very nice solution! It spent some time to convince myself that it
addresses the general problem and not just produces the desired output
by chance, so to speak.

I did find a need for a minor improvement:

SELECT ProjectID, SubledgerID
FROM (SELECT ProjectID,
SubledgerID =
(SELECT C.SubledgerID
FROM SubledgerID C
LEFT JOIN SubledgerProduct AC
ON C.SubledgerID = AC.SubledgerID
AND AC.ProductID IN (SELECT AB.ProductID
FROM ProjectProduct AB
WHERE AB.ProjectID = B.ProjectID)
GROUP BY C.SubledgerID
HAVING COUNT(AC.ProductID) = (SELECT COUNT(*)
FROM ProjectProduct AB
WHERE AB.ProjectID = B.ProjectID)
AND COUNT(AC.ProductID) = (SELECT COUNT(*)
FROM SubledgerProduct AC
WHERE AC.SubledgerID =
C.SubledgerID)
)
FROM Project B) B
WHERE SubledgerID IS NOT NULL

As I understand the problem, it may be possible that there are projects
that does not map to subledgers and vice versa.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Thanks for the improvement and converting the script.

Good luck,
Shervin

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94002C72C286Yazorman@.127.0.0.1...
> Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> > This is my solution. I used your first example (because of its shorter
> > names!)
> > It's funny that we don't need table A at all. All we need is its
> > relations to B and C.
> Very nice solution! It spent some time to convince myself that it
> addresses the general problem and not just produces the desired output
> by chance, so to speak.
> I did find a need for a minor improvement:
> SELECT ProjectID, SubledgerID
> FROM (SELECT ProjectID,
> SubledgerID =
> (SELECT C.SubledgerID
> FROM SubledgerID C
> LEFT JOIN SubledgerProduct AC
> ON C.SubledgerID = AC.SubledgerID
> AND AC.ProductID IN (SELECT AB.ProductID
> FROM ProjectProduct AB
> WHERE AB.ProjectID = B.ProjectID)
> GROUP BY C.SubledgerID
> HAVING COUNT(AC.ProductID) = (SELECT COUNT(*)
> FROM ProjectProduct AB
> WHERE AB.ProjectID = B.ProjectID)
> AND COUNT(AC.ProductID) = (SELECT COUNT(*)
> FROM SubledgerProduct AC
> WHERE AC.SubledgerID =
> C.SubledgerID)
> )
> FROM Project B) B
> WHERE SubledgerID IS NOT NULL
> As I understand the problem, it may be possible that there are projects
> that does not map to subledgers and vice versa.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Wow,

Those are some well worked querys.

Thank you very much for them, they seem to address my situation.

In my specific probelem, there are no Projects that do not map to
Subledgers, but Subledger to Project does not alway match - the
Subeldger table will always map directly to all possible combinations of
Products, but not all possible combinations of Products will map to a
Project.

Simon Withers

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

many to many query with 1 row per result?

I have three tables
Student
-ID
-FirstName
<etc.>
Test
-ID
-Date
<etc.>
StudentTests
- TestID
- ChildID
- Grade
What I want to do is have a query that returns a student's information,
with a list of tests they've had. So, one row per student would be
ideal. I thought about changing table layout to have a fixed number of
tests, but I want to be able to change the number of tests pretty much
dynamically. Then I thought I could change table layout dynamically
(adding / removing columns as needed, and using dynamic SQL) but then I
thought that ... might not be the best idea :)
Right now I have this query
SELECT S.ID, S.FirstName, S.LastName, T.ID, T.Date, ST.Grade
FROM dbo.Student S
LEFT JOIN dbo.StudentTests ST ON ST.StudentID = S.ID
LEFT JOIN dbo.Test T ON T.ID = ST.TestID
ORDER BY S.LastName
Let's say there are 5 tests, I get 5 rows, I'm not super excited by
that, but I'm also not coming up with a way to change it.
So is there a way to do this?Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You might also want to learn ISO-11179 data element naming rules so
that when you do write DDL, it will be usable. Only one student,
magical "id" in the tables, reserved words, compound table names (do
you really say "mid-term exams" or "mid-term studenttest"?), etc. This
is sloppy even for a personal pseudo-code. Is this what you meant?
CREATE TABLE Students
(student_id INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE TestForms
(test_id INTEGER NOT NULL PRIMARY KEY,
test_date DATETIME NOT NULL,
.);
CREATE TABLE Exams
(student_id INTEGER NOT NULL
REFERENCES Students(student_id)
ON UPDATE CASCADE,
test_id INTEGER NOT NULL
REFERENCES TestForms(test_id)
ON UPDATE CASCADE,
test_grade CHAR(1) DEFAULT 'I' NOT NULL
CHECK ( test_grade IN ('I', 'W', 'A', 'B', 'C', 'D', 'F'),
PRIMARY KEY (student_id, test_id));
Did you know that you are supposed to do reports in application and not
the database in a tiered architecture?
Did you know that a table has a fixed number of columns by definition?
You are describing a report, which should be done in the front end.
Finally, you got something right! Dynamic SQL is a way of saying that
you have no idea what to do, so you will let someone else decide at run
time.
If the tests are attributes of an exam schedule, then each one gets a
column. just like height, weight and eye color would in a table that
models a person. If the tests are separate entities related to a
student, then each one gets a row in a gradebook or exams table.
This report is called a cross tabs and has been for the last 250+
years. So of course Microsoft calls it a PIVOT to be different. Her
is a quick way to write it in portable, standard SQL:
SELECT S1.student_id,
MAX(CASE WHEN T1.test_id = 1 THEN T1.test_grade ELSE '' END)
AS exam_1,
MAX(CASE WHEN T1.test_id = 2 THEN T1.test_grade ELSE '' END)
AS exam_2,
MAX(CASE WHEN T1.test_id = 3 THEN T1.test_grade ELSE '' END)
AS exam_3,
MAX(CASE WHEN T1.test_id = 4 THEN T1.test_grade ELSE '' END)
AS exam_4,
MAX(CASE WHEN T1.test_id = 5 THEN T1.test_grade ELSE '' END)
AS exam_5
FROM Students AS S1, Exams AS T1
WHERE S1.student_id = T1.student_id
GROUP BY S1.student_id;|||There are users of this group who have varying degrees of expertise.
If these questions bother you so much - STOP RESPONDING!!!! Aren't you
afraid that by being so abusive you are going to hurt your book sales?
--CELKO-- wrote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> You might also want to learn ISO-11179 data element naming rules so
> that when you do write DDL, it will be usable. Only one student,
> magical "id" in the tables, reserved words, compound table names (do
> you really say "mid-term exams" or "mid-term studenttest"?), etc. This
> is sloppy even for a personal pseudo-code. Is this what you meant?
> CREATE TABLE Students
> (student_id INTEGER NOT NULL PRIMARY KEY,
> ..);
> CREATE TABLE TestForms
> (test_id INTEGER NOT NULL PRIMARY KEY,
> test_date DATETIME NOT NULL,
> ..);
> CREATE TABLE Exams
> (student_id INTEGER NOT NULL
> REFERENCES Students(student_id)
> ON UPDATE CASCADE,
> test_id INTEGER NOT NULL
> REFERENCES TestForms(test_id)
> ON UPDATE CASCADE,
> test_grade CHAR(1) DEFAULT 'I' NOT NULL
> CHECK ( test_grade IN ('I', 'W', 'A', 'B', 'C', 'D', 'F'),
> PRIMARY KEY (student_id, test_id));
>
> Did you know that you are supposed to do reports in application and not
> the database in a tiered architecture?
>
> Did you know that a table has a fixed number of columns by definition?
> You are describing a report, which should be done in the front end.
>
> Finally, you got something right! Dynamic SQL is a way of saying that
> you have no idea what to do, so you will let someone else decide at run
> time.
>
> If the tests are attributes of an exam schedule, then each one gets a
> column. just like height, weight and eye color would in a table that
> models a person. If the tests are separate entities related to a
> student, then each one gets a row in a gradebook or exams table.
> This report is called a cross tabs and has been for the last 250+
> years. So of course Microsoft calls it a PIVOT to be different. Her
> is a quick way to write it in portable, standard SQL:
> SELECT S1.student_id,
> MAX(CASE WHEN T1.test_id = 1 THEN T1.test_grade ELSE '' END)
> AS exam_1,
> MAX(CASE WHEN T1.test_id = 2 THEN T1.test_grade ELSE '' END)
> AS exam_2,
> MAX(CASE WHEN T1.test_id = 3 THEN T1.test_grade ELSE '' END)
> AS exam_3,
> MAX(CASE WHEN T1.test_id = 4 THEN T1.test_grade ELSE '' END)
> AS exam_4,
> MAX(CASE WHEN T1.test_id = 5 THEN T1.test_grade ELSE '' END)
> AS exam_5
> FROM Students AS S1, Exams AS T1
> WHERE S1.student_id = T1.student_id
> GROUP BY S1.student_id;|||Please send the table DDL, and a mock up of what your desired results looks
like.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<jwsolt@.gmail.com> wrote in message
news:1150901561.790662.157270@.b68g2000cwa.googlegroups.com...
>I have three tables
> Student
> -ID
> -FirstName
> <etc.>
> Test
> -ID
> -Date
> <etc.>
> StudentTests
> - TestID
> - ChildID
> - Grade
> What I want to do is have a query that returns a student's information,
> with a list of tests they've had. So, one row per student would be
> ideal. I thought about changing table layout to have a fixed number of
> tests, but I want to be able to change the number of tests pretty much
> dynamically. Then I thought I could change table layout dynamically
> (adding / removing columns as needed, and using dynamic SQL) but then I
> thought that ... might not be the best idea :)
> Right now I have this query
> SELECT S.ID, S.FirstName, S.LastName, T.ID, T.Date, ST.Grade
> FROM dbo.Student S
> LEFT JOIN dbo.StudentTests ST ON ST.StudentID = S.ID
> LEFT JOIN dbo.Test T ON T.ID = ST.TestID
> ORDER BY S.LastName
> Let's say there are 5 tests, I get 5 rows, I'm not super excited by
> that, but I'm also not coming up with a way to change it.
> So is there a way to do this?
>|||"Gary Gibbs" <ggibbs@.aahs.org> wrote in message
news:1150903867.254646.88710@.u72g2000cwu.googlegroups.com...
> There are users of this group who have varying degrees of expertise.
> If these questions bother you so much - STOP RESPONDING!!!! Aren't you
> afraid that by being so abusive you are going to hurt your book sales?
Here, here...|||jwsolt@.gmail.com wrote:
> What I want to do is have a query that returns a student's information,
> with a list of tests they've had. So, one row per student would be
> snipped
> Let's say there are 5 tests, I get 5 rows, I'm not super excited by
> that, but I'm also not coming up with a way to change it.
> So is there a way to do this?
>
I don't completely understand what you're looking for. You say you want
to return the student's information with a list of tests they've taken.
You then say that if there are five tests, you get back five records,
which is not what you want. If a student has taken five tests, and you
want a list of the tests that student has taken, why would you not want
five records returned?|||It seems that a cross-tab report presentation is what is desired. It should
happen at the client -or investigate SQL Reporting Services.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OA3IfGVlGHA.4772@.TK2MSFTNGP03.phx.gbl...
> jwsolt@.gmail.com wrote:
> I don't completely understand what you're looking for. You say you want
> to return the student's information with a list of tests they've taken.
> You then say that if there are five tests, you get back five records,
> which is not what you want. If a student has taken five tests, and you
> want a list of the tests that student has taken, why would you not want
> five records returned?|||> I don't completely understand what you're looking for. You say you want
> to return the student's information with a list of tests they've taken.
> You then say that if there are five tests, you get back five records,
> which is not what you want. If a student has taken five tests, and you
> want a list of the tests that student has taken, why would you not want
> five records returned?
They want a pivot table of sorts.
e.g. Student Test 1 Test 2 Test 3 Test 4 Test 5
a 90 72 NULL 54 99
b NULL NULL NULL NULL 71
The problem is that SQL does not lend itself to figuring out how far across
you have to go. This kind of data shaping is definitely better for
client-side reporting tools.
http://www.aspfaq.com/2462|||Aaron Bertrand [SQL Server MVP] wrote:
> They want a pivot table of sorts.
> e.g. Student Test 1 Test 2 Test 3 Test 4 Test 5
> a 90 72 NULL 54 99
> b NULL NULL NULL NULL 71
> The problem is that SQL does not lend itself to figuring out how far acros
s
> you have to go. This kind of data shaping is definitely better for
> client-side reporting tools.
> http://www.aspfaq.com/2462
>
Ahhh, I get it now. His use of the phrase "list of tests" threw me...|||Personally I think his sales have been in decline for sometime and he tries
to drum up sales by being so abusive and ignorant - loudest voice gets
noticed and all that; if only he realised what a fool he has made of
himself.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Gary Gibbs" <ggibbs@.aahs.org> wrote in message
news:1150903867.254646.88710@.u72g2000cwu.googlegroups.com...
> There are users of this group who have varying degrees of expertise.
> If these questions bother you so much - STOP RESPONDING!!!! Aren't you
> afraid that by being so abusive you are going to hurt your book sales?
> --CELKO-- wrote:
>sql

Many thanks in advance! - Simple Date function - Please Help!

Hi All,

Does anyone know how to return a date the sql query analyser like (Aug 2, 2004)

Right now, the following statement returns (Aug 2, 2004 8:40PM). This is now good because I need to do a specific date search that doesn't include the time.

Many thanks in advance!!
Brad

--------------
declare @.today DateTime
Select @.today = GetDate()
print @.todaylook at the Convert function...something like...

select convert(varchar, getdate(), 6)

|||Heres a tutorial that I thought was helpful
http://www.easerve.com/developer/tutorials/asp-net-tutorials-dates.aspx

many small queries vs one large query

I just had a discussion with my boss, we are running a query that fetches
about 5000 objects from the database using MSSQL and DotNet 2.0.
My boss thinks that the recommendation of keeping the connections open for
as short a time as possible means that one shoule open a connection, fetch
one row, close the connection, open another connection, fetch one row and
close the connection and so on.
I belive that the extra overhead of opening and closing connections all the
time, of generating execution plans for each query, and running the queries
many times gives his solution a large performance hit. My recommendation
will be to fetch all the rows needed in one large SQL query.
I've done several other projects where I have proven this to be true, there
is indeed a large performance hit from generating many small queries instead
of one large. But my boss just says "no" and disagrees.
Can you give me some good arguments and/or point me to some best practice
documents that describe this so I can convince my boss he's wrong?
Kind Regards,
Allan Ebdrup
FUT: microsoft.public.sqlserver.programming"Allan Ebdrup" <ebdrup@.noemail.noemail> wrote in message
news:%23kIhprXWGHA.3332@.TK2MSFTNGP02.phx.gbl...
>I just had a discussion with my boss, we are running a query that fetches
>about 5000 objects from the database using MSSQL and DotNet 2.0.
> My boss thinks that the recommendation of keeping the connections open for
> as short a time as possible means that one shoule open a connection, fetch
> one row, close the connection, open another connection, fetch one row and
> close the connection and so on.
> I belive that the extra overhead of opening and closing connections all
> the time, of generating execution plans for each query, and running the
> queries many times gives his solution a large performance hit. My
> recommendation will be to fetch all the rows needed in one large SQL
> query.
> I've done several other projects where I have proven this to be true,
> there is indeed a large performance hit from generating many small queries
> instead of one large. But my boss just says "no" and disagrees.
> Can you give me some good arguments and/or point me to some best practice
> documents that describe this so I can convince my boss he's wrong?
>
Don't argue with your boss. Implement it both ways and show him.
David|||"Allan Ebdrup" <ebdrup@.noemail.noemail> wrote in
news:#kIhprXWGHA.3332@.TK2MSFTNGP02.phx.gbl:

> I've done several other projects where I have proven this to be true,
> there is indeed a large performance hit from generating many small
> queries instead of one large. But my boss just says "no" and disagrees
I would have submitted one large bulk query as well.
Perhaps multiple small queries would reduce the time a table is locked?
Any other solutions? Maybe open a connection - execute a long running SP...
and fetch the results after the fact?|||> Don't argue with your boss. Implement it both ways and show him.
Hi David
Unfortunately it's him who's doing the implementing.|||My post just now seems to have got lost (wonder where it went).
To summarise what I said:
your boss is making a massive mistake. I've seen this tactic destroy a
large application.
it's fine at first, your queries all run nearly instantaneously, and
you'll load test it later, no need to worry about that right now.
then a table gets big. suddenly the instant query takes 50 milliseconds
to get the data. not a problem if you're only doing one select. if
you're doing 5000 separate ones it takes 5000x50 milliseconds.|||IF your query is executing 5000 times against the same tables and retrieving
the same columns with selection criteria against the same columns, then
running a single query to retreive all 5000 rows would be faster, without
question.
i.e.
select employee_id, employee_name from employees
Will have a connection open for much less time than cycling through each
employee with:
select employee_id, employee_name from employee where employee_id =
'SOMEID'
The first one will also use less CPU on the database, less IO, less network
traffic, etc. You can play with the code to control how you open the
dataset once you have retrieved it, but the first SQL will almost always be
faster. The only exception I can think of is if your client does not have
enough memory to handle the entire dataset.
If you have more complicated logic, and you are running against all
different tables, and combining this into one query means extensive joins
and business logic in the where clause, then you need to test each approach
and see what the performance gain is with the view.
Opening and closing connections for each communication wiht the database is
usually most efficient in my experience. Connection pooling manages the
resources really well. The connections do not really get opened and closed,
rather they stay open and available, but the client is able to free up some
of the resources associated with them between each call. This assumes that
you actually need to execute multiple queries to begin with.
Making 5000 seperate calls to the database will use much more network
resources than making a single call to the database to retreive the same
data. That part is a no brainer. Dependign on what you have for SQL, it
will not necessarily need to generate a plan every time (one would hope you
are using stored procedures with parameters and the plans are cached).
However, there would be some small overhead associated with running each
command.
"Allan Ebdrup" <ebdrup@.noemail.noemail> wrote in message
news:%23kIhprXWGHA.3332@.TK2MSFTNGP02.phx.gbl...
> I just had a discussion with my boss, we are running a query that fetches
> about 5000 objects from the database using MSSQL and DotNet 2.0.
> My boss thinks that the recommendation of keeping the connections open for
> as short a time as possible means that one shoule open a connection, fetch
> one row, close the connection, open another connection, fetch one row and
> close the connection and so on.
> I belive that the extra overhead of opening and closing connections all
the
> time, of generating execution plans for each query, and running the
queries
> many times gives his solution a large performance hit. My recommendation
> will be to fetch all the rows needed in one large SQL query.
> I've done several other projects where I have proven this to be true,
there
> is indeed a large performance hit from generating many small queries
instead
> of one large. But my boss just says "no" and disagrees.
> Can you give me some good arguments and/or point me to some best practice
> documents that describe this so I can convince my boss he's wrong?
> Kind Regards,
> Allan Ebdrup
> FUT: microsoft.public.sqlserver.programming
>|||SQL Server is quite capable of handling long connections, and as long as
they're actually doing something they make perfect sense. Many short lasting
connections may seem to put less stress on the server, but in fact the
overhead of many connections versus a single connection, when calls to the
server constitute a single business operation, must be multiplied with the
number of connections to give a true estimate of the actual (and expected)
stress on the server.
Will puts it very simply (and very true): 5000 connections where each lasts
50ms might kick the server pretty hard. 2nd grade mathematics.
ML
http://milambda.blogspot.com/|||If the calls to the database are made against isolated objects (i.e. against
different tables or to procedures that target different tables), then
individual connections may benefit from being initiated asynchronously. But
it would make more sense in issuing a single call to related objects for a
single business operation. At least IMHO.
ML
http://milambda.blogspot.com/|||All posts made a good point and I agree that your boss is wrong. It's a no
wonder he is a not a SQL Programmer but he is your boss. He signs your
paycheck so you may not have a choice. I often had to bow to what my boss
wants, hate it and it sucks but what can I do.
Grant
Who gives a {censored} if I am wrong.
"Allan Ebdrup" <ebdrup@.noemail.noemail> wrote in message
news:%23kIhprXWGHA.3332@.TK2MSFTNGP02.phx.gbl...
>I just had a discussion with my boss, we are running a query that fetches
>about 5000 objects from the database using MSSQL and DotNet 2.0.
> My boss thinks that the recommendation of keeping the connections open for
> as short a time as possible means that one shoule open a connection, fetch
> one row, close the connection, open another connection, fetch one row and
> close the connection and so on.
> I belive that the extra overhead of opening and closing connections all
> the time, of generating execution plans for each query, and running the
> queries many times gives his solution a large performance hit. My
> recommendation will be to fetch all the rows needed in one large SQL
> query.
> I've done several other projects where I have proven this to be true,
> there is indeed a large performance hit from generating many small queries
> instead of one large. But my boss just says "no" and disagrees.
> Can you give me some good arguments and/or point me to some best practice
> documents that describe this so I can convince my boss he's wrong?
> Kind Regards,
> Allan Ebdrup
> FUT: microsoft.public.sqlserver.programming
>|||If responsiveness of your application is important, you might consider to
fetch a certain amount of objects per fetch (say 100 objects, depending on
the size of your objects), instead of fetching 5000 objects together.
If you are going to get 5000 objects at once, while responsiveness is not a
problem, you should fetch all rows using one query.
Martin C K Poon
Senior Analyst Programmer
====================================
"Allan Ebdrup" <ebdrup@.noemail.noemail> bl
news:%23kIhprXWGHA.3332@.TK2MSFTNGP02.phx.gbl g...
> I just had a discussion with my boss, we are running a query that fetches
> about 5000 objects from the database using MSSQL and DotNet 2.0.
> My boss thinks that the recommendation of keeping the connections open for
> as short a time as possible means that one shoule open a connection, fetch
> one row, close the connection, open another connection, fetch one row and
> close the connection and so on.
> I belive that the extra overhead of opening and closing connections all
the
> time, of generating execution plans for each query, and running the
queries
> many times gives his solution a large performance hit. My recommendation
> will be to fetch all the rows needed in one large SQL query.
> I've done several other projects where I have proven this to be true,
there
> is indeed a large performance hit from generating many small queries
instead
> of one large. But my boss just says "no" and disagrees.
> Can you give me some good arguments and/or point me to some best practice
> documents that describe this so I can convince my boss he's wrong?
> Kind Regards,
> Allan Ebdrup
> FUT: microsoft.public.sqlserver.programming
>sql

Many fields update from a Group by Clause

Hi All,

In Oracle, I can easily make this query :

UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)
FROM t2
WHERE t2.f5=t1.f6)
WHERE f5='Something'

I cannot seem to be able to do the same thing with MS-SQL. There are
only 2 ways I've figured out, and I fear performance cost in both cases,
which are these :
1)
UPDATE t1 SET f1=(SELECT AVG(f3)
FROM t2
WHERE t2.f5=t1.f6)
WHERE f5='Something'

and then the same statement but with f2, and

2)
UPDATE t1 SET f1=(SELECT AVG(f3)
FROM t2
WHERE t2.f5=t1.f6),
f2=(SELECT SUM(f4)
FROM t2
WHERE t2.f5=t1.f6)
WHERE f5='Something'

Is there a way with MS-SQL to do the Oracle equivalent in this case ?

Thanks,

MichelHi

You could try something like

UPDATE t
SET f1 = dt.avgcol, f2 = dt.sumcol
FROM t1 JOIN ( SELECT f5, AVG(f3) AS avgcol, SUM(f4) AS SumCol
FROM t2
WHERE f5 = 'Something'
GROUP BY f5 ) dt ON dt.f5=t1.f6

John
"Michel" <Michel@.askme.com> wrote in message
news:mGfeb.1763$r.377054@.news20.bellglobal.com...
> Hi All,
> In Oracle, I can easily make this query :
> UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)
> FROM t2
> WHERE t2.f5=t1.f6)
> WHERE f5='Something'
> I cannot seem to be able to do the same thing with MS-SQL. There are
> only 2 ways I've figured out, and I fear performance cost in both cases,
> which are these :
> 1)
> UPDATE t1 SET f1=(SELECT AVG(f3)
> FROM t2
> WHERE t2.f5=t1.f6)
> WHERE f5='Something'
> and then the same statement but with f2, and
> 2)
> UPDATE t1 SET f1=(SELECT AVG(f3)
> FROM t2
> WHERE t2.f5=t1.f6),
> f2=(SELECT SUM(f4)
> FROM t2
> WHERE t2.f5=t1.f6)
> WHERE f5='Something'
> Is there a way with MS-SQL to do the Oracle equivalent in this case ?
> Thanks,
> Michel|||It works fine, thank you very much. I did try something similar just before,
but I made the links in the where clause instead of the joins, and for some
reason it wasn't working.

Well, thanks, now it works fine and I'm saving 50% time on the queries!

Michel

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f798cee$0$8767$ed9e5944@.reading.news.pipex.n et...
> Hi
> You could try something like
> UPDATE t
> SET f1 = dt.avgcol, f2 = dt.sumcol
> FROM t1 JOIN ( SELECT f5, AVG(f3) AS avgcol, SUM(f4) AS SumCol
> FROM t2
> WHERE f5 = 'Something'
> GROUP BY f5 ) dt ON
dt.f5=t1.f6
> John
> "Michel" <Michel@.askme.com> wrote in message
> news:mGfeb.1763$r.377054@.news20.bellglobal.com...
> > Hi All,
> > In Oracle, I can easily make this query :
> > UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)
> > FROM t2
> > WHERE t2.f5=t1.f6)
> > WHERE f5='Something'
> > I cannot seem to be able to do the same thing with MS-SQL. There are
> > only 2 ways I've figured out, and I fear performance cost in both cases,
> > which are these :
> > 1)
> > UPDATE t1 SET f1=(SELECT AVG(f3)
> > FROM t2
> > WHERE t2.f5=t1.f6)
> > WHERE f5='Something'
> > and then the same statement but with f2, and
> > 2)
> > UPDATE t1 SET f1=(SELECT AVG(f3)
> > FROM t2
> > WHERE t2.f5=t1.f6),
> > f2=(SELECT SUM(f4)
> > FROM t2
> > WHERE t2.f5=t1.f6)
> > WHERE f5='Something'
> > Is there a way with MS-SQL to do the Oracle equivalent in this case ?
> > Thanks,
> > Michel|||Is that 50% over Oracle :)

"Michel" <Michel@.askme.com> wrote in message
news:0Bgeb.1931$r.386539@.news20.bellglobal.com...
> It works fine, thank you very much. I did try something similar just
before,
> but I made the links in the where clause instead of the joins, and for
some
> reason it wasn't working.
> Well, thanks, now it works fine and I'm saving 50% time on the queries!
> Michel
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3f798cee$0$8767$ed9e5944@.reading.news.pipex.n et...
> > Hi
> > You could try something like
> > UPDATE t
> > SET f1 = dt.avgcol, f2 = dt.sumcol
> > FROM t1 JOIN ( SELECT f5, AVG(f3) AS avgcol, SUM(f4) AS SumCol
> > FROM t2
> > WHERE f5 = 'Something'
> > GROUP BY f5 ) dt ON
> dt.f5=t1.f6
> > John
> > "Michel" <Michel@.askme.com> wrote in message
> > news:mGfeb.1763$r.377054@.news20.bellglobal.com...
> > > Hi All,
> > > > In Oracle, I can easily make this query :
> > > > UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)
> > > FROM t2
> > > WHERE t2.f5=t1.f6)
> > > WHERE f5='Something'
> > > > I cannot seem to be able to do the same thing with MS-SQL. There
are
> > > only 2 ways I've figured out, and I fear performance cost in both
cases,
> > > which are these :
> > > 1)
> > > UPDATE t1 SET f1=(SELECT AVG(f3)
> > > FROM t2
> > > WHERE t2.f5=t1.f6)
> > > WHERE f5='Something'
> > > > and then the same statement but with f2, and
> > > > 2)
> > > UPDATE t1 SET f1=(SELECT AVG(f3)
> > > FROM t2
> > > WHERE t2.f5=t1.f6),
> > > f2=(SELECT SUM(f4)
> > > FROM t2
> > > WHERE t2.f5=t1.f6)
> > > WHERE f5='Something'
> > > > Is there a way with MS-SQL to do the Oracle equivalent in this case ?
> > > > Thanks,
> > > > Michel
> >

Manually Delete FullText Catalog

Hi all,
How can I manually delete a fulltext catalog, via query analyzer?
What is the command
I can't delete it via enterprise manager!
thanks in advance,
Fabio
I'm curious as to why you can't delete it in EM. This could be symptomatic
of larger problems.
To delete it I would use the following commands in Query Analyzer - where
test1234 is your catalog name.
declare @.int int
declare @.string varchar(200)
Create table holding
(
TABLE_OWNER sysname,
TABLE_NAME sysname,
FULLTEXT_KEY_INDEX_NAME sysname,
FULLTEXT_KEY_COLID int,
FULLTEXT_INDEX_ACTIVE int,
FULLTEXT_CATALOG_NAME sysname)
insert into holding
exec sp_help_fulltext_tables 'test1234'
select @.int = @.@.rowcount
while @.int>0
begin
select @.string='sp_fulltext_table ''' +table_name+''',''drop''' from holding
exec (@.string)
delete from holding
select @.int=@.int-1
end
exec sp_fulltext_catalog 'test1234','drop'
GO
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fabio" <fabio@.glb.com.br> wrote in message
news:OUGTUkSCFHA.520@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> How can I manually delete a fulltext catalog, via query analyzer?
> What is the command
> I can't delete it via enterprise manager!
> thanks in advance,
> Fabio
>
|||I don't know why I can't delete it in EM, whem I select the folder Full-Text
Catalog, the EM just freeze!
So I can't delete the Catalog or even know it name!!!
How Can I List the catalogs in my database?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uiCUS1TCFHA.1836@.tk2msftngp13.phx.gbl...
> I'm curious as to why you can't delete it in EM. This could be
symptomatic
> of larger problems.
> To delete it I would use the following commands in Query Analyzer - where
> test1234 is your catalog name.
> declare @.int int
> declare @.string varchar(200)
> Create table holding
> (
> TABLE_OWNER sysname,
> TABLE_NAME sysname,
> FULLTEXT_KEY_INDEX_NAME sysname,
> FULLTEXT_KEY_COLID int,
> FULLTEXT_INDEX_ACTIVE int,
> FULLTEXT_CATALOG_NAME sysname)
> insert into holding
> exec sp_help_fulltext_tables 'test1234'
> select @.int = @.@.rowcount
> while @.int>0
> begin
> select @.string='sp_fulltext_table ''' +table_name+''',''drop''' from
holding
> exec (@.string)
> delete from holding
> select @.int=@.int-1
> end
> exec sp_fulltext_catalog 'test1234','drop'
> GO
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Fabio" <fabio@.glb.com.br> wrote in message
> news:OUGTUkSCFHA.520@.TK2MSFTNGP09.phx.gbl...
>
|||try this sp_help_fulltext_catalogs
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fabio" <fabio@.glb.com.br> wrote in message
news:OuJa5HWCFHA.3596@.TK2MSFTNGP12.phx.gbl...
> I don't know why I can't delete it in EM, whem I select the folder
Full-Text[vbcol=seagreen]
> Catalog, the EM just freeze!
> So I can't delete the Catalog or even know it name!!!
> How Can I List the catalogs in my database?
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uiCUS1TCFHA.1836@.tk2msftngp13.phx.gbl...
> symptomatic
where
> holding
>

Manually delete FullText Catalog

Hi all,
How can I manually delete a fulltext catalog, via query analyzer?
What is the command
I can't delete it via enterprise manager!
thanks in advance,
Fabio
See sp_fulltext_catalog.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fabio" <fabio@.glb.com.br> wrote in message news:eLCvRlTCFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> How can I manually delete a fulltext catalog, via query analyzer?
> What is the command
> I can't delete it via enterprise manager!
> thanks in advance,
> Fabio
>

Manually delete FullText Catalog

Hi all,
How can I manually delete a fulltext catalog, via query analyzer?
What is the command
I can't delete it via enterprise manager!
thanks in advance,
FabioSee sp_fulltext_catalog.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fabio" <fabio@.glb.com.br> wrote in message news:eLCvRlTCFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> How can I manually delete a fulltext catalog, via query analyzer?
> What is the command
> I can't delete it via enterprise manager!
> thanks in advance,
> Fabio
>

Manually delete FullText Catalog

Hi all,
How can I manually delete a fulltext catalog, via query analyzer?
What is the command
I can't delete it via enterprise manager!
thanks in advance,
FabioSee sp_fulltext_catalog.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fabio" <fabio@.glb.com.br> wrote in message news:eLCvRlTCFHA.3120@.TK2MSFTNGP12.phx.gbl...[vb
col=seagreen]
> Hi all,
> How can I manually delete a fulltext catalog, via query analyzer?
> What is the command
> I can't delete it via enterprise manager!
> thanks in advance,
> Fabio
>[/vbcol]

Manipulate the 'deleted record' flag on DBF files

Hi,

I am using OLE DB provider for Foxpro (VFPOLEDB.1) to query DBF files. I need to migrate the content of these files to a SQL Server 2005 database.

These DBF files have some (actually a lot) records marked as deleted using the DBF 'deleted' flag. When I submit a SELECT command to the OLE DB Provider, it returns me all the non-deleted records from the file.

It is very Ok as long as the 'deleted' rows actually have no more business value, but in my case, I need to do some processing on them, and even to migrate their data.

What are the options available for me to be able to query and differentiate the 'deleted' records ?

Thank you in advance,

Bertrand Larsy

Hi Bertrand,

Here's some example VB code to work with the deleted status of a row:

Try

Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn1.Open()
'-- Make some VFP data to play with
Dim cmd1 As New OleDbCommand( _
"Create Table TestDBF (Field1 I, Field2 C(10))", cn1)
Dim cmd2 As New OleDbCommand( _
"Insert Into TestDBF Values (1, 'Hello')", cn1)
Dim cmd3 As New OleDbCommand( _
"Insert Into TestDBF Values (2, 'World')", cn1)
Dim cmd4 As New OleDbCommand( _
"Delete From TestDBF Where Field1 = 1", cn1)
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()
cmd4.ExecuteNonQuery()
cn1.Close()

Dim cn2 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn2.Open()

Dim cmd5 As New OleDbCommand( _
"Select * From TestDBF", cn2)
Dim da1 As New OleDbDataAdapter(cmd5)
Dim ds1 As New DataSet
Dim dr1 As DataRow
da1.Fill(ds1)
For Each dr1 In ds1.Tables(0).Rows
Console.WriteLine( _
dr1.Item(0).ToString() & ", " & dr1.Item(1).ToString)
Next
Console.ReadLine()
cn2.Close()

Dim cn3 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn3.Open()

Dim cmd6 As New OleDbCommand( _
"Set Deleted Off", cn3)
cmd6.ExecuteNonQuery()
Dim cmd7 As New OleDbCommand( _
"Select Deleted('TestDBF') As IsDeleted, TestDBF.* From TestDBF", cn3)
Dim da2 As New OleDbDataAdapter(cmd7)
Dim ds2 As New DataSet
Dim dr2 As DataRow
da2.Fill(ds2)
For Each dr2 In ds2.Tables(0).Rows
Console.WriteLine( _
dr2.Item(0).ToString() & ", " & dr2.Item(1).ToString() & ", " & dr2.Item(2).ToString())
Next
Console.ReadLine()
cn2.Close()

Catch e As Exception
MsgBox(e.ToString())
End Try

|||

Thank you,

This is indeed a workable solution.

Now, is there a way to perform the same work in a T-SQL script ?

I tried, but could not manage to "chain" successfully a 'SET DELETED OFF' statement and a SELECT with OPENQUERY or OPENDATASOURCE:

SELECT [name]

FROM OPENQUERY(linkedServer,'SELECT name FROM Members')

This returns all records, but not the deleted ones

SELECT [name]

FROM OPENQUERY(linkedServer,'SET Deleted OFF;

SELECT name FROM Members')

This produces the error '

Msg 7357, Level 16, State 2, Line 1

Cannot process the object "SET Deleted OFF;

SELECT name FROM Members". The OLE DB provider "VFPOLEDB" for linked server "linkedServer" indicates that either the object has no columns or the current user does not have permissions on that object.

'

When doing it with an EXECUTE statement as pass-through query on a linked server (using OLE DB provider for FoxPro, of course), it says it successfully executes, but does not return the result of the select query:

DECLARE @.Query varchar(max)

SET @.Query='SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

This returns all records, but not the deleted ones

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF' + CHAR(13) + 'SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

This produces the query output 'Command(s) completed successfully.', but does not return any result.

Kind regards,

Bertrand Larsy

|||

Hi Bertrand,

I don't have time to try it now but it might work with a semicolon: "Set Deleted Off;Select * From MyTable..."

|||

Neither of the 2 following samples give any result:

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF;SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF;' + CHAR(13) + 'SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

Anyway, thank you for your answers, I will make a CLR stored procedure of your first reply.

Kind regards,

Bertrand Larsy

Managing Triggers

How can I query to see all of the triggers that have been disabled or
enabled' Is there a way to do this using query analyzer or using a gui tool
?
THanks RichardTry,
select
object_name(parent_obj) as table_name,
[name] as trigger_name
from
sysobjects
where
xtype = 'TR'
and objectproperty([id], 'ExecIsTriggerDisabled') = 1
go
AMB
"Richard" wrote:

> How can I query to see all of the triggers that have been disabled or
> enabled' Is there a way to do this using query analyzer or using a gui to
ol?
> THanks Richard

Management Studio: when opening a table, any way to sort the table ?

In SQL Server 2000 Enterprise Manager when you open a table you can open it
with a query "order by" to sort the table by a column.
In the SQL Server 2005 Management Studio when you open a table, is there any
way to sort it by any of the column ?
Thank you
> In the SQL Server 2005 Management Studio when you open a table, is there
> any way to sort it by any of the column ?
My suggestion is to write...
SELECT * FROM table_name ORDER BY column_name;
...in a Query Editor window. Open Table is a bad joke.
|||I'll second that Aaron!
TheSQLGuru
President
Indicium Resources, Inc.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eFIRE0TrHHA.1172@.TK2MSFTNGP03.phx.gbl...
> My suggestion is to write...
> SELECT * FROM table_name ORDER BY column_name;
> ...in a Query Editor window. Open Table is a bad joke.
>
|||Thank you.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ukOnG1TrHHA.1296@.TK2MSFTNGP06.phx.gbl...
> Yes - click on the buttons in the top left hand corner to get the SQL,
> Diagram and Criteria. You can do the sorting by selecting the column in
> the diagram (check the box) then select the ascending/descending option in
> the grid for the column. Alternatively just code the SQL directly in the
> SQL pane.
> HTH,
> Paul Ibison
>

Management Studio: when opening a table, any way to sort the table ?

In SQL Server 2000 Enterprise Manager when you open a table you can open it
with a query "order by" to sort the table by a column.
In the SQL Server 2005 Management Studio when you open a table, is there any
way to sort it by any of the column ?
Thank you> In the SQL Server 2005 Management Studio when you open a table, is there
> any way to sort it by any of the column ?
My suggestion is to write...
SELECT * FROM table_name ORDER BY column_name;
...in a Query Editor window. Open Table is a bad joke.|||Yes - click on the buttons in the top left hand corner to get the SQL,
Diagram and Criteria. You can do the sorting by selecting the column in the
diagram (check the box) then select the ascending/descending option in the
grid for the column. Alternatively just code the SQL directly in the SQL
pane.
HTH,
Paul Ibison|||I'll second that Aaron!
TheSQLGuru
President
Indicium Resources, Inc.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eFIRE0TrHHA.1172@.TK2MSFTNGP03.phx.gbl...
> My suggestion is to write...
> SELECT * FROM table_name ORDER BY column_name;
> ...in a Query Editor window. Open Table is a bad joke.
>|||Thank you.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ukOnG1TrHHA.1296@.TK2MSFTNGP06.phx.gbl...
> Yes - click on the buttons in the top left hand corner to get the SQL,
> Diagram and Criteria. You can do the sorting by selecting the column in
> the diagram (check the box) then select the ascending/descending option in
> the grid for the column. Alternatively just code the SQL directly in the
> SQL pane.
> HTH,
> Paul Ibison
>