failed to create crystal query engine

Hi,
when trying to run crystal reports from within application the error:
'Crystal.CRPE.Application' Failed to create the Crystal Query Engine. appears.
The full install has been done and is version 8.5
any help appreciatedOpen the report and do verify database
or try it here
http://support.businessobjects.com/

Failed Login for a WorkGroup when attempting to run a query

Hi all,
I have an application which works with SQL Server. When the applicationattempts to load a page it needs to run certain queries. When itattempts to run a particular query it fails but I catch the exceptionand then log it. The following is logged:
System.Data.Ole.Db.OleDbException: Login failed for user 'CWAMB01DWH01\ASPENT'.
Now what I don't understand is that this is the work group that ASPNETis run under, and I am running other queries to the database via SQLServer authentication. Why am I getting a failed login for thisworkgroup? Do I need to create a new Login for the Server in SQLServer, and then create a new user for the database with the sameusername as the Workgroup name? If so, then how does the password workfor the SQL Server as the workgroup (CWAMB01DWH01\ASPENT) obviouslydoesn't have a password.
Thanks, and I hope I have explain my problem clear enough.
Tryst
There are two permissions in SQL Server database permissions in the database and server permissions you create under security in Enterprise Manager. Hope this helps.

Failed insert query

Hi

I'm trying to port some data from one database table to another
database table on the same server.

This is the query I am using:

-->
INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005 00:00',
ISNULL(Notes,'')
from olddatabase.dbo.contactevents
WHERE Exists (SELECT ContactID FROM newdatabase.dbo.contacts)
<---

This is the error I'm getting:

-->
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_ContactEvents_Contacts'. The conflict occurred in database
'newdatabase', table 'Contacts', column 'ContactID'.
The statement has been terminated.
<---

There is a relationship between the contacts table (Primary key
ContactID) and the contactsevent (foreign key ContactID) table. I guess
the error being flagged up here is that some contacts don't exist in
the new database, therefore referential intergretory won't allow it
being copied. I thought I could get around this using:
"WHERE Exists (SELECT ContactID FROM newdatabase.dbo.contacts)"
Note I've also tried:
"WHERE Exists (SELECT * FROM newdatabase.dbo.contacts)"

What am I doing wrong?

Many Thanks!

AlexYour subquery always evaluates to TRUE, so it's not filtering the data
- you need to link it to the outer table (see "Correlated Subqueries"
in Books Online):

...
from olddatabase.dbo.contactevents o
WHERE Exists (
SELECT *
FROM newdatabase.dbo.contacts n
where o.ContactID = n.ContactID
)

Simon|||Simon thanks...!

I checked books online, thanks for the reference.
Looking at the conditional statement you gave me:

WHERE Exists (
SELECT *
FROM newdatabase.dbo.contacts n
where o.ContactID = n.ContactID
)

... could you please clarify what 'o'' and 'n'' are?

I've tried now tried the below statement, which makes for sense to me
after your advice, unfortunately I still get the same error:

WHERE EXISTS
(SELECT ContactID FROM newdatabase.dbo.contacts
WHERE ContactID IN (SELECT ContactID FROM olddatabase.dbo.contacts))

I guess I still haven't got the hang of it!

Cheers!

Alex|||You query return all rows from olddatabase.dbo.contactevents, without
checking for the existance in newdatabase.dbo.contacts.

It aslo not advisible to use exists as it is ineffecient.

you can try this query:

INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005
00:00',ISNULL(Notes,'')
from olddatabase.dbo.contactevents
INNER JOIN newdatabase.dbo.contacts
ON newdatabase.dbo.contacts.ContactID =
olddatabase.dbo.contactevents.ContactID

please let me know if u have any questions

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||o and n are table aliases - instead of typing out the full table name
every time, it's easier to use an alias, and it often makes the code
more readable (see "Using Table Aliases" in Books Online). As for your
query, try this:

INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005 00:00',
ISNULL(Notes,'')
from olddatabase.dbo.contactevents o
WHERE EXISTS (
SELECT *
FROM newdatabase.dbo.contacts n
WHERE o.ContactID = n.ContactID
)

Or you may find this clearer:

INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005 00:00',
ISNULL(Notes,'')
from olddatabase.dbo.contactevents
WHERE ContactID IN (
SELECT ContactID
FROM newdatabase.dbo.contacts
)

I suspect that your query is mixing these two forms.

Simon|||Simon and Chandra

Thank you very much for your help!

Alex|||On Thu, 04 Aug 2005 12:17:11 GMT, Chandra wrote:

>You query return all rows from olddatabase.dbo.contactevents, without
>checking for the existance in newdatabase.dbo.contacts.
>It aslo not advisible to use exists as it is ineffecient.

Hi Chandra,

EXISTS inefficient? This is the first time that I hear that. In fact, I
always hear the opposite that it is very efficient since it'll stop
searching as soon as the first match is found, whereas other techniques
have to process all the data.

Can you post a repro script (or point me to one somewhere on the web)
that shows how EXISTS is less efficient than any of it's equivalents?

>you can try this query:
>INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
>ContactID, DateEntered, EventDate, Description)
>select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005
>00:00',ISNULL(Notes,'')
>from olddatabase.dbo.contactevents
>INNER JOIN newdatabase.dbo.contacts
>ON newdatabase.dbo.contacts.ContactID =
>olddatabase.dbo.contactevents.ContactID

It's highly probably that this query will work, but you can't be totally
sure. As the OP didn't post the DDL for the table, you can't be totally
sure that the join to newdatabase.dbo.contacts will never result in more
than one row. And if it ever does, then your query will either insert
duplicates in newdatabase.dbo.contactevents, or (if a key is properly
declared) result in a primary key violation.

I'd definitely use EXISTS in this case. And I'd change the dates to an
unambiguous format ('20050101' or '2005-01-01T00:00:00').

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Fail to connect DB in SQL Query Analyser with sa

When you registered in EM, In Properties tab,
change the Sql Server registration to "SQl Server and
Windows" from "Windows only" .
Hope this help...if not please let me know by reply.

>--Original Message--
>When I use the "connect" function in SQL Query Analyser
with "sa" account. I got error message " Not associated
with a trusted SQL Server connection". I don't know what
does it mean? pls help
>
It means that when you installed MSDE you did not specify mixed mode security. By default MSDE is installed with (only) Integrated Security. Check the setup doc for instructions about how to change the security settings.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Grey" <ericyum@.i-cable.com> wrote in message news:Om6%23Od4GEHA.4008@.TK2MSFTNGP10.phx.gbl...
When I use the "connect" function in SQL Query Analyser with "sa" account. I got error message " Not associated with a trusted SQL Server connection". I don't know what does it mean? pls help
|||I install cannot change. the error messge of my previous mail is the same.
pls help
"Rachan Terrell" <anonymous@.discussions.microsoft.com> ?
news:18f5901c41bf6$8316f0c0$a501280a@.phx.gbl ?...
> When you registered in EM, In Properties tab,
> change the Sql Server registration to "SQl Server and
> Windows" from "Windows only" .
> Hope this help...if not please let me know by reply.
>
> with "sa" account. I got error message " Not associated
> with a trusted SQL Server connection". I don't know what
> does it mean? pls help

Faiied Query

I am running SQL Server 2005 Express on the backend and Access 2000 on the front end.
This query fails and it is not clear why?

SELECT DISTINCTROW tblTutoringHours.[Site ID] AS Expr1, tblTutoringHours.[Student ID] AS Expr2, Sum(tblTutoringHours.Hours) AS [Tutoring Hours], Min(tblTutoringHours.Level) AS [Start Level], Max(tblTutoringHours.Level) AS [End Level], [End Level]-[Start Level] AS Gain, Max([Date Graduated] Is Not Null)*(-1) AS GainGrad, Max([Drop ID]=8)*(-1) AS GainPromoted
FROM tblSites, tblStudents, tblTutoringHours
WHERE ((([tblSites].[Database Totals])=Yes))
GROUP BY tblTutoringHours.[Site ID], tblTutoringHours.[Student ID]
HAVING (((Sum([tblTutoringHours].[Hours]))>0))
WITH OWNERACCESS OPTION;

Doesn't look like you have all of the non-aggregate fields in the GROUP BY statement.|||

This calculated column contains columns that are not aggregrated and NOT in the GROUP BY:

[End Level]-[Start Level] AS Gain

This just won't work as written:

Max([Date Graduated] Is Not Null)*(-1) AS GainGrad

It could be revised as:

max( isnull( [Date Graduated], 0 ) * (-1) AS GainGrad

Fact Table SQL Query - adding a prefix to a value

Hi there, I have a question regarding a query to extract measures from a fact table. The fact table from the source system contains delivery notes with turnover in one dataset. The primary key is the delivery note number plus a consecutive number. The same table contains also the cancellations of the delivery note with the same turnover and the same primary key as the delivery note, only differed by another consecutive number. The cancellation is represented by another column which contains either a "0" or a "1" (cancellation no/yes).

The problem is that I'd like to change the turnover value for cancellations with a prefix instead of using another column in the fact table. Cause then I'd be able to group those numbers...

Is there a SQL function that allows me to

Example datasets:

Code Snippet

ConsNo DelNNo Canc Turnover Amount

001 200 0 1000 500

002 200 1 1000 500

I'd like to achieve this output by a query:

Code Snippet

ConsNo DelNNo Turnover Amount

001 200 1000 500

002 200 -1000 -500

Any ideas? I've heard of a function called "decode" but I think it doesn't work in SQL Server 2005...

Just create a view that depending on the Canc column multiplies the Turnover and Amount by 1 or -1.|||But how to use IF-statement and mathematical functions within a SELECT-statement? That would be T-SQL right?
|||

In SQL you can do something like:

select ConsNo,

DelNNo,

Turnover = case

when Canc = 0

then Turnover

else Turnover * -1

end,

Amount = case

when Canc = 0

then Amount

else Amount * -1

end

from <table name>

There is also the possibility to use isnull and nullif to simulate the previous cases.

|||

Tiago Rente wrote:

In SQL you can do something like:

select ConsNo,

DelNNo,

Turnover = case

when Canc = 0

then Turnover

else Turnover * -1

end,

Amount = case

when Canc = 0

then Amount

else Amount * -1

end

from <table name>

There is also the possibility to use isnull and nullif to simulate the previous cases.

It works, but when I add the GROUP BY function I always get an error message that "Canc" and "Turnover" aren't groupable!

Surprisingly I didn't even select the "Canc"-attribute, and I don't use Turnover within the GROUP-function.

|||

Unfortunately with this solution you have to copy the case statement to the group by section. In Sybase IQ you could use the name of the column in the group by without repeting the code.

In SQL Server the other option is to create a User Define Function that receives 2 columns (Canc, Turnover) or (Canc, Amount) and returns the correct value. This way you still need to copy the call to the UDF in the group by, but is less error prune. However, this will cost you in performance, since the UDF will be executed for each row in the table (as if you had open a cursor ).

Or you can create a view and then do the group by to the result of the view, this way you do not need to repeat the case or UDF in the group by since you already have a column name to do the group by.

|||

Tiago Rente wrote:

Unfortunately with this solution you have to copy the case statement to the group by section. In Sybase IQ you could use the name of the column in the group by without repeting the code.

In SQL Server the other option is to create a User Define Function that receives 2 columns (Canc, Turnover) or (Canc, Amount) and returns the correct value. This way you still need to copy the call to the UDF in the group by, but is less error prune. However, this will cost you in performance, since the UDF will be executed for each row in the table (as if you had open a cursor ).

Or you can create a view and then do the group by to the result of the view, this way you do not need to repeat the case or UDF in the group by since you already have a column name to do the group by.

I tried to use the case-statement in the GROUP BY function but the result is the same. Weird...

|||

Summing the case statements should do the trick.

Code Snippet

select ConsNo,

DelNNo,

Turnover = SUM(case

when Canc = 0

then Turnover

else Turnover * -1

end),

Amount = SUM(case

when Canc = 0

then Amount

else Amount * -1

end)

from <table name>

GROUP BY

ConsNo

, DelNo

Extremly bad performance Stored Procedures

The last few days my sqlserver executes stored procedures badly.
When I run the Query Analyzer to execute a certain stored procedure it takes more than 2 minutes to execute the procedure. When I copy the contents of de SP to Query analyser to run it as an sql statement it find's the results within a second.
This behavior dissapear's after a while and comes back randomly.
I had the problem Friday afternoon then tuesday and now again.
Between these day's my sqlserver works fine.
Can anybody please help me with this problem.Have you tried using the "with recompile" option ? Your query plan is probably based on an outdated data distribution or schema. Running the "with recompile" option will regenerate the query plan. Also, are you parameters to the stored procedure vary enough that the execution plans change ? Do a comparison in query analyzer - using show execution plan.|||I was able to elimante the problem by altering de SP.
In the SP there where more than 4 joins to the same table.
When I made a user defined function and replaced those joins with this function, it all works fine.

But one question remains. How is it possible that the query analyser didn't have problems with the joins but de SP did have?|||Did you try the recompile ? Sometimes, if your table(s) involved in the query change enough - the query plan needs to change as well. When you run it in query analyzer, the query plan is generated dynamically. For the sp, it could still be using the original query plan when you created it. That is why I suggested to run the 2 in query analyzer with the "show execution plan".

Extremely Slow Trigger Problem

I have an after update trigger that is killing me and I can't figure out why
.
When I run an update query that fires the trigger and look at the execution
plan, 85% of the cost is in the OPEN __0005 statement. I'm only updating 1
row, so this makes no sense to me. The I/O cost of opening the cursor is in
the 1100 range. Anyone got any ideas - I'm fresh out.
CREATE TRIGGER trgUpdate ON ITEMTASKS
FOR UPDATE
AS
DECLARE
@.ITE_ID INT,
@.TAS_ID INT,
@.STATUS TINYINT,
@.OLDSTATUS TINYINT
IF (@.@.ROWCOUNT = 0) RETURN
IF ( UPDATE( ITS_STATUS) ) BEGIN
SET NOCOUNT ON
DECLARE __0005 CURSOR LOCAL FAST_FORWARD FOR
SELECT I.ITE_ID, I.ITS_STATUS, I.TAS_ID, D.ITS_STATUS
FROM INSERTED I, DELETED D
WHERE I.ITE_ID=D.ITE_ID AND I.TAS_ID = D.TAS_ID
OPEN __0005
FETCH NEXT FROM __0005
INTO @.ITE_ID, @.STATUS, @.TAS_ID, @.OLDSTATUS
WHILE (@.@.FETCH_STATUS = 0 ) BEGIN
IF (@.STATUS = 6 ) BEGIN
UPDATE ITEMS
SET ITE_STATUS = 6
WHERE ITE_ID = @.ITE_ID
END
ELSE IF (@.STATUS = 2) AND ((SELECT TAS_ISLAST FROM TASKS WHERE TAS_ID =
@.TAS_ID)=1) BEGIN
UPDATE ITEMS
SET ITE_STATUS = 2
WHERE ITE_ID = @.ITE_ID
END
ELSE BEGIN
UPDATE ITEMS
SET ITE_STATUS = 1
WHERE ITE_ID = @.ITE_ID
END
EXEC ADD2SUMMARY_ @.ITE_ID, @.TAS_ID, @.STATUS, @.OLDSTATUS
FETCH NEXT FROM __0005
INTO @.ITE_ID, @.STATUS, @.TAS_ID, @.OLDSTATUS
END
CLOSE __0005
DEALLOCATE __0005
SET NOCOUNT OFF
ENDI strongly recommend you don't use cursors at all in triggers. Why
can't you re-write this and the contents of Add2Summary as set-based
SQL?
If you need more help then please come back with DDL, sample data
INSERT statements and show your required end result.
David Portas
SQL Server MVP
--|||Supposing that the cursor is necesary, I have two suggestions:
First -
Declare de cursor static and read_only
Second - Use EXIST in the following statement
-- ELSE IF (@.STATUS = 2) AND ((SELECT TAS_ISLAST FROM TASKS WHERE TAS_ID =
@.TAS_ID)=1) BEGIN
ELSE IF (@.STATUS = 2) AND exists(SELECT * FROM TASKS WHERE TAS_ID =
@.TAS_ID and TAS_ISLAST = 1) BEGIN
I do not know anything about the sp being executed, but the three updates
statements can be implemented without the use of the cursor.
AMB
"Arghknork" wrote:

> I have an after update trigger that is killing me and I can't figure out w
hy.
> When I run an update query that fires the trigger and look at the executi
on
> plan, 85% of the cost is in the OPEN __0005 statement. I'm only updating
1
> row, so this makes no sense to me. The I/O cost of opening the cursor is
in
> the 1100 range. Anyone got any ideas - I'm fresh out.
> CREATE TRIGGER trgUpdate ON ITEMTASKS
> FOR UPDATE
> AS
> DECLARE
> @.ITE_ID INT,
> @.TAS_ID INT,
> @.STATUS TINYINT,
> @.OLDSTATUS TINYINT
>
> IF (@.@.ROWCOUNT = 0) RETURN
> IF ( UPDATE( ITS_STATUS) ) BEGIN
> SET NOCOUNT ON
> DECLARE __0005 CURSOR LOCAL FAST_FORWARD FOR
> SELECT I.ITE_ID, I.ITS_STATUS, I.TAS_ID, D.ITS_STATUS
> FROM INSERTED I, DELETED D
> WHERE I.ITE_ID=D.ITE_ID AND I.TAS_ID = D.TAS_ID
> OPEN __0005
> FETCH NEXT FROM __0005
> INTO @.ITE_ID, @.STATUS, @.TAS_ID, @.OLDSTATUS
> WHILE (@.@.FETCH_STATUS = 0 ) BEGIN
> IF (@.STATUS = 6 ) BEGIN
> UPDATE ITEMS
> SET ITE_STATUS = 6
> WHERE ITE_ID = @.ITE_ID
> END
> ELSE IF (@.STATUS = 2) AND ((SELECT TAS_ISLAST FROM TASKS WHERE TAS_ID =
> @.TAS_ID)=1) BEGIN
> UPDATE ITEMS
> SET ITE_STATUS = 2
> WHERE ITE_ID = @.ITE_ID
> END
> ELSE BEGIN
> UPDATE ITEMS
> SET ITE_STATUS = 1
> WHERE ITE_ID = @.ITE_ID
> END
> EXEC ADD2SUMMARY_ @.ITE_ID, @.TAS_ID, @.STATUS, @.OLDSTATUS
> FETCH NEXT FROM __0005
> INTO @.ITE_ID, @.STATUS, @.TAS_ID, @.OLDSTATUS
> END
> CLOSE __0005
> DEALLOCATE __0005
> SET NOCOUNT OFF
> END
>|||Agreed, but I inherited the code and it's in production this way. It has
been working okay up until about a w ago, and I think there is an
underlying problem here I'm not seeing. Even with a cursor, it shouldn't
cost so much to open that cursor with 1 row in the inserted/deleted tables.
"David Portas" wrote:

> I strongly recommend you don't use cursors at all in triggers. Why
> can't you re-write this and the contents of Add2Summary as set-based
> SQL?
> If you need more help then please come back with DDL, sample data
> INSERT statements and show your required end result.
> --
> David Portas
> SQL Server MVP
> --
>

Extremely Slow Query Times

Is there something that I can do to improve the query times when using

Excel to query my AS2005 cube? It's EXTREMELY slow, even if I'm

the only one querying the cube.

The AS server should not be a bottle neck (Windows 2003 x64-bit, dual

core AMD Opteron, 7 + gigs of memory, etc.). I fee like our cube

is very small with little data s well.

In addition to the slow query times when using Excel, using the Browser

in Visual Studio locally on the server results in queries taking longer

to execute than I would expect.

What are some things that I can do to improve performance?

This is just a shot in the dark, but have you specified all the member property relationships between the attributes on your dimensions correctly? If you've created your dimensions using the wizard then the vast majority (possibly all) of your attributes will be directly linked to the key attribute only, and this can lead to less than optimal performance.

Here's an example of what I mean: say you have a Geography dimension with Continent, Country, State and City attributes and Address as the key attribute. You know there's a many-to-one relationship between Address and City, City and State, State and Country and Country and Continent, but by default the wizard will only create relationships on Address and City, Address and State, Address and Country and Address and Continent. What you need to do is go to the dimension editor in VS, then in the Attributes pane on the left hand side drag and drop attributes onto other attributes to create these relationships (AS knows about transitive relationships too, so you can delete ones like Address and Continent). Once you've done this then redesign your aggregations (usage-based optimisation might be a good idea too in the medium term) and reprocess, and you should see an improvement in performance.

Chris

|||

Chris,

I know what you're referring to, but I don't really understand how to implement it correctly.

Take

the following as my example. Let's say I have Product SKU and SKU

Description as available attributes in my Products dimension. While

editing the Products dimension, I notice that I can drag SKU

Description to create a relationship under Product SKU. I can also do

the opposite and drag Product SKU to create a relationship under SKU

Description. However, I can't do

both. Therefore, what is the difference between the 2 relationships.

Ultimately, a Product SKU can have only one SKU Description and

vice-versa.

Thanks!|||

In this case, yes, you have a 1:1 relationship between Product SKU and SKU Description, but I believe it's still beneficial to put the relationship in (probably by making Description a property of Product SKU, if the latter is the key attribute of your dimension). Relationships are useful for AS when it tries to design aggregations, use aggregations during querying, and for working out which attributes 'exist' with each other, all of which will improve query performance. Defining 1:1 relationships aren't going to have such a big impact though; do you have any 1:M relationships such as Product Category to Product SKU, Year to Month etc? Defining them in the dimension is likely to have a much more obvious effect.

Extremely Slow Query Times

Is there something that I can do to improve the query times when using

Excel to query my AS2005 cube? It's EXTREMELY slow, even if I'm

the only one querying the cube.

The AS server should not be a bottle neck (Windows 2003 x64-bit, dual

core AMD Opteron, 7 + gigs of memory, etc.). I fee like our cube

is very small with little data s well.

In addition to the slow query times when using Excel, using the Browser

in Visual Studio locally on the server results in queries taking longer

to execute than I would expect.

What are some things that I can do to improve performance?

This is just a shot in the dark, but have you specified all the member property relationships between the attributes on your dimensions correctly? If you've created your dimensions using the wizard then the vast majority (possibly all) of your attributes will be directly linked to the key attribute only, and this can lead to less than optimal performance.

Here's an example of what I mean: say you have a Geography dimension with Continent, Country, State and City attributes and Address as the key attribute. You know there's a many-to-one relationship between Address and City, City and State, State and Country and Country and Continent, but by default the wizard will only create relationships on Address and City, Address and State, Address and Country and Address and Continent. What you need to do is go to the dimension editor in VS, then in the Attributes pane on the left hand side drag and drop attributes onto other attributes to create these relationships (AS knows about transitive relationships too, so you can delete ones like Address and Continent). Once you've done this then redesign your aggregations (usage-based optimisation might be a good idea too in the medium term) and reprocess, and you should see an improvement in performance.

Chris

|||

Chris,

I know what you're referring to, but I don't really understand how to implement it correctly.

Take

the following as my example. Let's say I have Product SKU and SKU

Description as available attributes in my Products dimension. While

editing the Products dimension, I notice that I can drag SKU

Description to create a relationship under Product SKU. I can also do

the opposite and drag Product SKU to create a relationship under SKU

Description. However, I can't do

both. Therefore, what is the difference between the 2 relationships.

Ultimately, a Product SKU can have only one SKU Description and

vice-versa.

Thanks!|||

In this case, yes, you have a 1:1 relationship between Product SKU and SKU Description, but I believe it's still beneficial to put the relationship in (probably by making Description a property of Product SKU, if the latter is the key attribute of your dimension). Relationships are useful for AS when it tries to design aggregations, use aggregations during querying, and for working out which attributes 'exist' with each other, all of which will improve query performance. Defining 1:1 relationships aren't going to have such a big impact though; do you have any 1:M relationships such as Product Category to Product SKU, Year to Month etc? Defining them in the dimension is likely to have a much more obvious effect.

Extremely Slow Query Times

Is there something that I can do to improve the query times when using Excel to query my AS2005 cube? It's EXTREMELY slow, even if I'm the only one querying the cube.
The AS server should not be a bottle neck (Windows 2003 x64-bit, dual core AMD Opteron, 7 + gigs of memory, etc.). I fee like our cube is very small with little data s well.
In addition to the slow query times when using Excel, using the Browser in Visual Studio locally on the server results in queries taking longer to execute than I would expect.
What are some things that I can do to improve performance?

This is just a shot in the dark, but have you specified all the member property relationships between the attributes on your dimensions correctly? If you've created your dimensions using the wizard then the vast majority (possibly all) of your attributes will be directly linked to the key attribute only, and this can lead to less than optimal performance.

Here's an example of what I mean: say you have a Geography dimension with Continent, Country, State and City attributes and Address as the key attribute. You know there's a many-to-one relationship between Address and City, City and State, State and Country and Country and Continent, but by default the wizard will only create relationships on Address and City, Address and State, Address and Country and Address and Continent. What you need to do is go to the dimension editor in VS, then in the Attributes pane on the left hand side drag and drop attributes onto other attributes to create these relationships (AS knows about transitive relationships too, so you can delete ones like Address and Continent). Once you've done this then redesign your aggregations (usage-based optimisation might be a good idea too in the medium term) and reprocess, and you should see an improvement in performance.

Chris

|||Chris,

I know what you're referring to, but I don't really understand how to implement it correctly.

Take the following as my example. Let's say I have Product SKU and SKU Description as available attributes in my Products dimension. While editing the Products dimension, I notice that I can drag SKU Description to create a relationship under Product SKU. I can also do the opposite and drag Product SKU to create a relationship under SKU Description. However, I can't do both. Therefore, what is the difference between the 2 relationships. Ultimately, a Product SKU can have only one SKU Description and vice-versa.

Thanks!|||

In this case, yes, you have a 1:1 relationship between Product SKU and SKU Description, but I believe it's still beneficial to put the relationship in (probably by making Description a property of Product SKU, if the latter is the key attribute of your dimension). Relationships are useful for AS when it tries to design aggregations, use aggregations during querying, and for working out which attributes 'exist' with each other, all of which will improve query performance. Defining 1:1 relationships aren't going to have such a big impact though; do you have any 1:M relationships such as Product Category to Product SKU, Year to Month etc? Defining them in the dimension is likely to have a much more obvious effect.

extremely slow query

I have this querys, and when I run it, it took 15 minutes trowing the result, for the momment event is a table with 101 tuples and leftjets are near to 700 tuples, im gonna chech if I see where is the bottleneck.

Code Snippet

/**********************************************************************/
/* Jet Veto Cut 2
* leftJets jetbs should have Pt not bigger then maxAllowedPtForOtherJets
* see Hadronic Top Cut 2
* m_maxAllowedPtForOtherJets: ptOJets
*/
/*
* TTreeCut::SelectTopCombination, m_theTopComb
* min of m_okTopComb
*/

create view mTopComb
As
select j.*
from topComb as j
where (abs(sqrt(abs((j.j1Ee+j.j2Ee + j.Ee)*(j.j1Ee+j.j2Ee +j.Ee) -
((j.j1px +j.j2px + j.px)*(j.j1px +j.j2px + j.px) +
(j.j1py +j.j2py + j.py)*(j.j1py +j.j2py + j.py) +
(j.j1pz +j.j2pz + j.pz)*(j.j1pz +j.j2pz + j.pz))))
- 174.3))
=
(select min(abs(sqrt(abs((t.j1Ee+t.j2Ee + t.Ee)*(t.j1Ee+t.j2Ee +t.Ee) -
((t.j1px +t.j2px + t.px)*(t.j1px +t.j2px + t.px) +
(t.j1py +t.j2py + t.py)*(t.j1py +t.j2py + t.py) +
(t.j1pz +t.j2pz + t.pz)*(t.j1pz +t.j2pz + t.pz))))
- 174.3))
from topComb as t
where t.eventid=j.eventid)

GO

/*
* TTreeCut::SelectTopCombination, m_theLeftOverJets
* select m_okJets which are not contained in m_theTopComb
*/

create view leftjets
As
select distinct o.*
from okJets as o
where not exists (select o.idap from mtopcomb as j where j.idap=o.idap);

GO

create view jetVetoCut
AS
select distinct e.*
from events e
where not exists (select * from leftjets j where e.idevent=j.eventid and dbo.pt(j.idap)>70);

GO

The WHERE clause contains expressions that are extremely complex and that makes it very hard for the Query Optimizer to do accurate estimation. Typically column expressions at the WHERE clause cause optimziation problems, with plan choice or plan execution and they make the use of indexes impossible. I am guessing that for these reasons the query optimizer produces a suboptimal plan and hence your query is slow.

The following article describes a way to deal with complex expressions and enable the query optimizer to do proper estimation . I hope that you find this useful: http://blogs.msdn.com/queryoptteam/archive/2006/03/24/560089.aspx

Regards,

Leo Giakoumakis

|||

hi,

here's another alternative.. the idea of mTopCombComputed is to return the same result as your mTopCombView but the difference is that we minimized the query so that it would require less number of reads.

CREATE FUNCTION mTopCombComputed()
RETURNS @.mTopComb TABLE (
eventid int
, idap numeric -- just changed datatype according to your DDL
, Computed numeric -- just changed datatype according to your DDL
)
AS
BEGIN
INSERT
INTO @.mTopComb
SELECT j.eventid
, j.idap
, Computed = (abs(sqrt(abs((j.j1Ee+j.j2Ee + j.Ee)*(j.j1Ee+j.j2Ee +j.Ee) -
((j.j1px +j.j2px + j.px)*(j.j1px +j.j2px + j.px) +
(j.j1py +j.j2py + j.py)*(j.j1py +j.j2py + j.py) +
(j.j1pz +j.j2pz + j.pz)*(j.j1pz +j.j2pz + j.pz))))
- 174.3))
FROM topComb j
DELETE j
FROM @.mTopComb j INNER JOIN
(
SELECT eventid
, MIN(Computed) AS MinComputed
FROM @.mTopComb
GROUP BY
eventid
) t ON j.eventid = t.eventid
WHERE j.Computed <> t.MinComputed

RETURN
END
GO

ALTER VIEW leftjets
AS

SELECT DISTINCT
o.*
FROM okJets o LEFT OUTER JOIN
dbo.mTopCombComputed() c ON o.idap = c.idap
WHERE c.idap IS NULL

GO

ALTER VIEW jetVetoCut
AS

SELECT DISTINCT
e.*
FROM events e LEFT OUTER JOIN
leftjets j ON e.idevent = j.eventid
and dbo.pt(j.idap) > 70
WHERE j.eventid IS NULL

GO

Extremely slow Excel MDX

Using Excel as a client is most of the time exceedingly slow. For example writing a simple query of the type:

SELECT [Measures].[Some Measure] ON 0,
[Product].[Product-Version].[Product] ON 1
FROM [Cubename]

in Management studio is in Excel transformed to:

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Product].[Product-Version].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Cubename] WHERE ([Measures].[Some Measure])

which takes several times longer to execute. As one starts drilling down it becomes increasingly worse with excel producing MDX that takes 100:s of times longer to execute then if I handwrite the mdx. This is with a very simple cube where Some Measure is not a calculated member. I can't even begin to imagine how slow it would be with a more complex cube. Is there anything to be done about this, any guidelines to follow to make it easer for Excel to generate "normal" mdx?

I had similar problem with Excel and OWC when accessing OLAP Cubes. Unfortunately, unless you optimize your cube, there is nothing can be done with how Excel generate mdx to retrieve data. Excel and OWC is closed code, and recently Microsoft announced that they will be stopping new releases for OWC. Howerver, Excel PivotTable has new version, take a look at Office 2007 in Beta version. It generates more efficient MDXs compared to Office 2003/2000.

Downside, it will take another 2-3 years for Office 2007 to be as popular as Office 2003, so, distribution of your solution in Office 2007 might be an issue if u decide to switch to Office 2007 in larger scale enterprise.

|||Thanks. Is there anything special you have in mind when you say "unless you optimize your cube"? Or do you mean the "ordinary" optimizations one does to make the server work decently fast? I will try to have a look at the 2007 beta though.|||

As mentioned earlier, you can take a look at Office 2007 sending bit different MDX queries. You also take a look at the ProClarity recently aquired by Microsoft see if you get better performance using it.

Also make sure you install latest service pack - SP1. There has been some performance improvements in it. You will see event more performance improvements in upcoming service pack 2. Watch for announcements of Community Technology Preview (CTP) to get your hands on upcoming SP2.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Extremely simple query (I hope) but I can't solve it

Hi, I'm new in MDX and I have a request that I think is extremely simple, but I can't make it.

I have a dimension with a hierarchy, like this

[Dim1]

[Hier_Dim1]

All

Level1

SLevel12

SLevel13

Level 2

SLevel21

SSlevel211

SSLevel212

etc.

I want the values of a measure [Measures].[Total] on the childrens of SLevel21, I write this MDX

SELECT NON EMPTY { [Measures].[Total] } ON COLUMNS

, NON EMPTY { [Dim1].[Hier_Dim1].&[SLevel21].Children} ON ROWS

FROM [My Cube]

Result Set I expect is

SSLevel211 Total1

SSLevel212 Total2

But I have this

Level1 SLevel2 SSLevel211 Total1

Level1 SLevel2 SSLevel212 Total2

I want to use result set on a report that receive as a parameter the member of hierarchy selected, so the number of columns returned should't depend on parameter selected.

Regards

Julio Diaz

What you are seeing here is a "flattened" cellset. I don't know of anyway of suppressing these extra columns that are generated when the results are "flattened" (converted from a multi-dimensional to a 2 dimensional result), but what you can do is to create a calculated member which will give you a consistant column name that you can use in your report. So in the example below you would use the [Dim1_MbrName] column in your report to get the names of the children of the selected member.

WITH MEMBER [Measures].[Dim1_MbrName] AS [Dim1].[Hier_Dim1].CurrentMember.Name

SELECT NON EMPTY { [Measures].[Dim1_MbrName],[Measures].[Total] } ON COLUMNS

, NON EMPTY { [Dim1].[Hier_Dim1].&[SLevel21].Children} ON ROWS

FROM [My Cube]

|||

Thanks Darren, It works good!

Now I′m trying to pass [Dim1].[Hier_Dim1].&[SLevel21] as a parameter, but it is another topics my question was answered :)

Regards

Julio Diaz

Extremely Poor Query Performance - Identical DBs Different Performance

Hello Everyone,

I have a very complex performance issue with our production database.
Here's the scenario. We have a production webserver server and a
development web server. Both are running SQL Server 2000.

I encounted various performance issues with the production server with a
particular query. It would take approximately 22 seconds to return 100
rows, thats about 0.22 seconds per row. Note: I ran the query in single
user mode. So I tested the query on the Development server by taking a
backup (.dmp) of the database and moving it onto the dev server. I ran
the same query and found that it ran in less than a second.

I took a look at the query execution plan and I found that they we're
the exact same in both cases.

Then I took a look at the various index's, and again I found no
differences in the table indices.

If both databases are identical, I'm assumeing that the issue is related
to some external hardware issue like: disk space, memory etc. Or could
it be OS software related issues, like service packs, SQL Server
configuations etc.

Here's what I've done to rule out some obvious hardware issues on the
prod server:
1. Moved all extraneous files to a secondary harddrive to free up space
on the primary harddrive. There is 55gb's of free space on the disk.
2. Applied SQL Server SP4 service packs
3. Defragmented the primary harddrive
4. Applied all Windows Server 2003 updates

Here is the prod servers system specs:
2x Intel Xeon 2.67GHZ
Total Physical Memory 2GB, Available Physical Memory 815MB
Windows Server 2003 SE /w SP1

Here is the dev serers system specs:
2x Intel Xeon 2.80GHz
2GB DDR2-SDRAM
Windows Server 2003 SE /w SP1

I'm not sure what else to do, the query performance is an order of
magnitude difference and I can't explain it. To me its is a hardware or
operating system related issue.

Any Ideas would help me greatly!

Thanks,
Brian T

*** Sent via Developersdex http://www.developersdex.com ***Brian
Before looking at hardware try running
UPDATE STATISTICS tablename for relevant tables with indexes
and see if it makes a difference to performance

Brian Tabios wrote:
> Hello Everyone,
> I have a very complex performance issue with our production database.
> Here's the scenario. We have a production webserver server and a
> development web server. Both are running SQL Server 2000.
> I encounted various performance issues with the production server with a
> particular query. It would take approximately 22 seconds to return 100
> rows, thats about 0.22 seconds per row. Note: I ran the query in single
> user mode. So I tested the query on the Development server by taking a
> backup (.dmp) of the database and moving it onto the dev server. I ran
> the same query and found that it ran in less than a second.
> I took a look at the query execution plan and I found that they we're
> the exact same in both cases.
> Then I took a look at the various index's, and again I found no
> differences in the table indices.
> If both databases are identical, I'm assumeing that the issue is related
> to some external hardware issue like: disk space, memory etc. Or could
> it be OS software related issues, like service packs, SQL Server
> configuations etc.
> Here's what I've done to rule out some obvious hardware issues on the
> prod server:
> 1. Moved all extraneous files to a secondary harddrive to free up space
> on the primary harddrive. There is 55gb's of free space on the disk.
> 2. Applied SQL Server SP4 service packs
> 3. Defragmented the primary harddrive
> 4. Applied all Windows Server 2003 updates
>
> Here is the prod servers system specs:
> 2x Intel Xeon 2.67GHZ
> Total Physical Memory 2GB, Available Physical Memory 815MB
> Windows Server 2003 SE /w SP1
> Here is the dev serers system specs:
> 2x Intel Xeon 2.80GHz
> 2GB DDR2-SDRAM
> Windows Server 2003 SE /w SP1
> I'm not sure what else to do, the query performance is an order of
> magnitude difference and I can't explain it. To me its is a hardware or
> operating system related issue.
> Any Ideas would help me greatly!
> Thanks,
> Brian T
> *** Sent via Developersdex http://www.developersdex.com ***|||I've seen this before and acutally it's quite common.

The key to your problem is different execution plans.

A SP can have several copies of a execution plan.

a) Different SET statements to the connection
b) You don't call the SP from QA with proper owner prefix (e.g. dbo)
c) In a multitple CPU environment you will have one scheduler (UMS) for
each SPID, and you might experience that you get the same exection plan
until your thread is closed.

Make sure that you update the statistics whenever you experience such
problems. The stats are stored in server, not in the databases.

So what do you do?
If a procedure gets slow, you can recompile the procedure with
sp_recompile. If this doesn't help, use DBCC FREEPROCCACHE and run the
procedure agin.

SP's are often recompiled in an OLTP environment. Common reason is
change in statistics. Sometimes the optimizer makes a poor choice in
execution plan due to variance in the parameteres it recevies when
recompiling. It's smart to look into what parameters are sent to the
procedure and see if there are great changes. Also pay attention to
complex procedures with if-else and case-statements. Keep it simple!

Regards,
Henrik

*** Sent via Developersdex http://www.developersdex.com ***

eXtremely Long Time in Execution Query

Hi all,

I have a query, rather complex one to deal with more than 1 million rows, used to run 40 minutes in SQL Server 2000 in query analyzer. Now, it has been 10 hours in SQL Server 2005 in management studio. And still has not finished yet! Anything can go wrong here. Basically nothing changes, except for I have my server upgrade from SQL Server 2000 to SQL Server 2005. Seems something is wrong crazy in SQL Server 2005. Any suggestions?

Thanks,

Ning

Reminds me of some hotfix descriptions - Have you checked knowledgebase articles for "slow query"? Here's an example that relates to fast forward-only queries:

"FIX: The query performance is very slow when you use a fast forward-only cursor to run a query in SQL Server 2005": http://support.microsoft.com/default.aspx/kb/926024

|||

I found the reason, but not sure why, either do I have a way to fix that.

In one of my select sentence, I have a field, nvarchar(2000), if I exclude that field in the select statement then everything is back to speed. Otherwise, we are cralwing like 1000 lines per minute, so 1 million lines will be 1000 minute ... (without that field we are at 100,000 per minute). I am not sure why a field can cause such a big deal 100 times in performance diff ...

Microsoft has to explain this ...

Thanks,

Ning

|||

I am wondering if it is joining to the table containing this field early in the query and so having to carry the up to 2K a row of data round (for a million rows - that's up to 2G). This would probably show as an explosion in the size of tempdb (as this is where it is likely to be caching this data during the query).

Was it just the field or the table providing the field which you removed from the query. If it was the table you might try putting it at the bottom of the from clause and using the FORCE ORDER Query hint. However this will prevent the query planner rearranging any of the tables so you might want to put them in the order specified by the plan for the fast query without that field in.

extreme help with query of 2 tables into 1 long table

2 tables user id is key
Table (A) 05_Users
user_id | first_name |last_name|title|dept
64|John|Doe|director|cis
65|Jane|Doe|ceo|fina
and
Table(B) 05_Users_Details
user_id | detail_cd | group_cd | detail_value
64|06|awdM0|null
64|07|awdD0|null
64|2005|awdY0|null
64|FreeText|awdTxt0|I enjoy work
64|10|awdM1|null
64|09|awdD1|null
64|2004|awdY1|null
64|FreeText|awdTxt1|still here
64|local|pfmLEVL1|null
64|natial|pfmLEVL1|null
64|aapm|pfmAAPM1|null
64|FreeText|pfmFREE1|profess
65|etc
I'm trying to creat a query that will give me all user information into one
long table with the group_cd as a 'column title' and detail_cd as 'column
value', but if it finds the 'column value' of FreeText then detail_value
should be 'column value'.
So the table would look like this.
user_id | first_name
|last_name|title|dept|awdM0|awdD0|awdY0|awdTxt0|aw dM1|awdD1|awdY1|awdTxt1|pfmLEVL1|pfmLEVL1|pfmAAPM1 |FREE TEXT
64|John|Doe|director|cis|06|07|2005|I enjoy work|10|09|2004|still
here|local|natial|aapm|profess
65|Jane|Doe|ceo|fina etc
Some users have more information than other users and in these cases the
'column value' can be left as blank.
I don't need a webpage, but if it will help, will use.
IF YOU HAVE A BETTER WAY TO GET ALL THE INFORMATION ANY SUGGESTIONS WOULD BE
GREAT!
On Tue, 7 Jun 2005 10:26:02 -0700, BIGLU wrote:
(snip)
>IF YOU HAVE A BETTER WAY TO GET ALL THE INFORMATION ANY SUGGESTIONS WOULD BE
>GREAT!
Hi BIGLU,
First: The format you used to describe your data makes it very hard to
read and understand and almost impossible to reproduce. For future
postings, please include CREATE TABLE and INSERT statements for table
structure and sample data, as described here: www.aspfaq.com/5006.
Second: What you're trying to achieve looks like a pivot, or cross-tab
query. The front end/presentation layer is actually the best place for
that task. If you have to do it on the server, then try if you can adapt
the following to your needs:
SELECT u.UserID,
MAX(CASE WHEN d.DetailCD = 'awdM0' THEN d.detailValue END) AS
awdM0,
MAX(CASE WHEN d.DetailCD = 'awdD0' THEN d.detailValue END) AS
awdD0,
....
FROM Users AS u
INNER JOIN UserDetails AS d
ON d.UserID = u.UserID
GROUP BY u.UserID
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
When you say "front end/presentatio layer" can I import and do in access or
excel? If so, can you give me a link where I can do this in any of these? I
know how to export (I think I do.lol), but I'll need help with crosstab, etc.
Or is this something I can do in asp.net? or a third party component?
Thanks
"Hugo Kornelis" wrote:

> On Tue, 7 Jun 2005 10:26:02 -0700, BIGLU wrote:
> (snip)
> Hi BIGLU,
> First: The format you used to describe your data makes it very hard to
> read and understand and almost impossible to reproduce. For future
> postings, please include CREATE TABLE and INSERT statements for table
> structure and sample data, as described here: www.aspfaq.com/5006.
> Second: What you're trying to achieve looks like a pivot, or cross-tab
> query. The front end/presentation layer is actually the best place for
> that task. If you have to do it on the server, then try if you can adapt
> the following to your needs:
> SELECT u.UserID,
> MAX(CASE WHEN d.DetailCD = 'awdM0' THEN d.detailValue END) AS
> awdM0,
> MAX(CASE WHEN d.DetailCD = 'awdD0' THEN d.detailValue END) AS
> awdD0,
> ....
> FROM Users AS u
> INNER JOIN UserDetails AS d
> ON d.UserID = u.UserID
> GROUP BY u.UserID
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 9 Jun 2005 09:42:06 -0700, LU wrote:

>Hugo,
>When you say "front end/presentatio layer" can I import and do in access or
>excel?
Hi LU,
I must admit that I have little expertise with respect toi front end
applications. But as far as I know, Access has some builtin
functionality to create a cross-tab table (look up "TRANSFORM" and
"PIVOT" in the online help, or use the crosstab query wizard). And Excel
can do crosstab reports as well.

>Or is this something I can do in asp.net?
Probably, but you'd better ask in a group for asp.net! <g>

>or a third party component?
Some third party applications that might help you generate the crosstab
at the server (though I still recommend against it!) may be found near
the end of this page: http://www.aspfaq.com/show.asp?id=2462
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Extracting XML data to columns during query

I'm working with an inherited SQL 2000 database (now moved to 2005)
that stores strings of XML in a text column. I'd like to avoid
changing the schema right now. Each row's XML data looks something
like:
<root><element id='5' name='bob' message='hello' /></root>
The exact attributes in the inner element are unknown, but what I
would like to be able to do is return them as columns in a query such
that I would get:
id name message
-- -- --
5 bob hello
Any thoughts?I was just now experimenting with something similar. Most of this is right
out of the MSDN help. This is reading from an external XML file.
-- create tables for later population using OPENXML.
create table Customers (CustomerID varchar(20) primary key,
ContactName varchar(20),
CompanyName varchar(20))
go
create table Orders( CustomerID varchar(20), OrderDate datetime)
go
declare @.xmlDocument xml
select @.xmlDocument = cast (x as xml)
from OpenRowset (bulk 'P:\SQL scripts\Examples\XML\SourceOfXml.xml',
single_blob) R (x)
select @.xmlDocument
-- Contents of the source XML file.
--
-- <?xml version="1.0" encoding="windows-1252" ?>
-- <ROOT>
-- <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
-- <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
-- <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
-- </Customers>
-- <Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
-- </Customers>
-- </ROOT>
declare @.docHandle int
exec sp_xml_preparedocument @.docHandle output, @.xmlDocument
-- Use OpenXML to provide rowset consisting of customer data.
insert Customers
select *
from OpenXML (@.docHandle, N'/ROOT/Customers')
with Customers
-- Use OpenXML to provide rowset consisting of order data.
insert Orders
select *
from OpenXML (@.docHandle, N'//Orders')
with Orders
-- Using OpenXML in a SELECT statement.
select *
from OpenXML (@.docHandle, N'/ROOT/Customers/Orders')
with (CustomerID nchar (5) '../@.CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
exec sp_xml_removedocument @.docHandle
/*
drop table Customers
drop table Orders
*/
"Brian Vallelunga" wrote:

> I'm working with an inherited SQL 2000 database (now moved to 2005)
> that stores strings of XML in a text column. I'd like to avoid
> changing the schema right now. Each row's XML data looks something
> like:
> <root><element id='5' name='bob' message='hello' /></root>
> The exact attributes in the inner element are unknown, but what I
> would like to be able to do is return them as columns in a query such
> that I would get:
> id name message
> -- -- --
> 5 bob hello
>
> Any thoughts?
>|||Hello Brian,

> I'm working with an inherited SQL 2000 database (now moved to 2005)
> that stores strings of XML in a text column. I'd like to avoid
> changing the schema right now. Each row's XML data looks something
> like:
> The exact attributes in the inner element are unknown, but what I
> would like to be able to do is return them as columns in a query such
> that I would get:
Theres lots of ways of doing this is the number of attributes are know, but
when they aren't, your going to have a hard time shaping them to a meaningfu
l
table. So whate exactly do you mean by "attributes are unknown?"
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||On Apr 3, 11:17 pm, Kent Tegels <kteg...@.develop.com> wrote:
> Hello Brian,
>
> Theres lots of ways of doing this is the number of attributes are know, bu
t
> when they aren't, your going to have a hard time shaping them to a meaning
ful
> table. So whate exactly do you mean by "attributes are unknown?"
> Thanks!
> Kent Tegels
> DevelopMentorhttp://staff.develop.com/ktegels/
Well, the xml data holds responses to online forms. Each form may have
different fields and each form field results in a single attribute key/
value pair. If a form has a first name and last name, the attributes
for these two would show up in the XML.
Obviously this makes it a bit more difficult than if the values were
known. However, for this, we can assume that any given set of data
pulled will be for one particular form, and will thus have the same
attributes in the XML data. I may just have to do this on the client
end, but thought I'd see if a SQL method was available.

Extracting XML data to columns during query

I'm working with an inherited SQL 2000 database (now moved to 2005)
that stores strings of XML in a text column. I'd like to avoid
changing the schema right now. Each row's XML data looks something
like:
<root><element id='5' name='bob' message='hello' /></root>
The exact attributes in the inner element are unknown, but what I
would like to be able to do is return them as columns in a query such
that I would get:
id name message
-- -- --
5 bob hello
Any thoughts?
I was just now experimenting with something similar. Most of this is right
out of the MSDN help. This is reading from an external XML file.
-- create tables for later population using OPENXML.
create table Customers (CustomerID varchar(20) primary key,
ContactName varchar(20),
CompanyName varchar(20))
go
create table Orders( CustomerID varchar(20), OrderDate datetime)
go
declare @.xmlDocument xml
select @.xmlDocument = cast (x as xml)
from OpenRowset (bulk 'P:\SQL scripts\Examples\XML\SourceOfXml.xml',
single_blob) R (x)
select @.xmlDocument
-- Contents of the source XML file.
-- <?xml version="1.0" encoding="windows-1252" ?>
-- <ROOT>
-- <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
-- <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
-- <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
-- </Customers>
-- <Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
-- </Customers>
-- </ROOT>
declare @.docHandle int
exec sp_xml_preparedocument @.docHandle output, @.xmlDocument
-- Use OpenXML to provide rowset consisting of customer data.
insert Customers
select *
from OpenXML (@.docHandle, N'/ROOT/Customers')
with Customers
-- Use OpenXML to provide rowset consisting of order data.
insert Orders
select *
from OpenXML (@.docHandle, N'//Orders')
with Orders
-- Using OpenXML in a SELECT statement.
select *
from OpenXML (@.docHandle, N'/ROOT/Customers/Orders')
with (CustomerID nchar (5) '../@.CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
exec sp_xml_removedocument @.docHandle
/*
drop table Customers
drop table Orders
*/
"Brian Vallelunga" wrote:

> I'm working with an inherited SQL 2000 database (now moved to 2005)
> that stores strings of XML in a text column. I'd like to avoid
> changing the schema right now. Each row's XML data looks something
> like:
> <root><element id='5' name='bob' message='hello' /></root>
> The exact attributes in the inner element are unknown, but what I
> would like to be able to do is return them as columns in a query such
> that I would get:
> id name message
> -- -- --
> 5 bob hello
>
> Any thoughts?
>
|||Hello Brian,

> I'm working with an inherited SQL 2000 database (now moved to 2005)
> that stores strings of XML in a text column. I'd like to avoid
> changing the schema right now. Each row's XML data looks something
> like:
> The exact attributes in the inner element are unknown, but what I
> would like to be able to do is return them as columns in a query such
> that I would get:
Theres lots of ways of doing this is the number of attributes are know, but
when they aren't, your going to have a hard time shaping them to a meaningful
table. So whate exactly do you mean by "attributes are unknown?"
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||On Apr 3, 11:17 pm, Kent Tegels <kteg...@.develop.com> wrote:
> Hello Brian,
>
> Theres lots of ways of doing this is the number of attributes are know, but
> when they aren't, your going to have a hard time shaping them to a meaningful
> table. So whate exactly do you mean by "attributes are unknown?"
> Thanks!
> Kent Tegels
> DevelopMentorhttp://staff.develop.com/ktegels/
Well, the xml data holds responses to online forms. Each form may have
different fields and each form field results in a single attribute key/
value pair. If a form has a first name and last name, the attributes
for these two would show up in the XML.
Obviously this makes it a bit more difficult than if the values were
known. However, for this, we can assume that any given set of data
pulled will be for one particular form, and will thus have the same
attributes in the XML data. I may just have to do this on the client
end, but thought I'd see if a SQL method was available.

Extracting the sql query in the stored procedure in asp.net

Hi,

I have set programmaticaly as follows for sql dataadapter

Commandtext="name of stored proc "

commandtype="stored proc"

now I want the query in the stored proc which i will store in the string .is there any way to get the query from sp progrmmaticaly?

Swati

Usesp_helptext