|
Colin Angus Mackay wrote: What about tables? Surely a forum requires multiple tables.
Nah - it can be done in one access table with 450 fields. Just call them Field1, Field2, Field3, ..., FieldN.
|
|
|
|
|
Hi All
I have 4 tables in a dataset:
ACTIVITY
ACTIVITY_PARTICIPANT related to ACTIVITY (nested)
PERSON related to ACTIVITY_PARTICIPANT (nested)
ROLE related to ACTIVITY_PARTICIPANT (nested)
if one person i related several times to one activity, with different roles, I get the "a child row has multiple parents" error.
I understand why I get the error
My question is:
How could I produce this xml
<DATASET>
<ACTIVITY>
<ACTIVITY_PARTICIPANT>
<ROLE ID="1" />
<PERSON NAME="PERSON A" />
</ACTIVITY_PARTICIPANT>
<ACTIVITY_PARTICIPANT>
<ROLE ID="2">
<PERSON NAME="PERSON A" />
</ROLE></ACTIVITY_PARTICIPANT>
</ACTIVITY>
</DATASET>
from my dataset. Is there a method, or do I have to write it myself ?
I guess I have to start with
ACTIVITY
ACTIVITY_PARTICIPANT related to ACTIVITY (nested)
PERSON related to ACTIVITY_PARTICIPANT (NOT nested)
ROLE related to ACTIVITY_PARTICIPANT (NOT nested)
Thans in advance
Kjetil
modified on Wednesday, June 11, 2008 8:55 AM
|
|
|
|
|
I created a table for keeping Configuration parameters. When i wanna get the value via stored proc, i got null. Could u check me my stored procedure pls.
CREATE PROCEDURE [dbo].[sp_GetConfigurationParameter]
@Key char(4),
@Type char(4),
@Value nchar OUTPUT
AS
SET NOCOUNT ON;
SELECT @Value=[Value] FROM VpConfParameters WHERE [Type]=@Type AND [Key]=@Key
To Try
declare @Value nchar
EXEC [sp_GetConfigurationParameter] 'DBNL','DEVE',@Value
select @Value
Result
NULL :'(
|
|
|
|
|
assuming that there is data that matches your query in the table, you could forego the output parameter and just select [Value] then your exec statement would be
declare @Value nchar
EXEC @value = [sp_GetConfigurationParameter] 'DBNL','DEVE',@Value
select @Value
I know that works in sql server 2k, and i'm fairly certain it wont work in oracle.
Alcohol the cause of and solution to all of life's problems.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
|
as far as i know you can still select values out of a usp in sql server 2k5.
Alcohol the cause of and solution to all of life's problems.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
You have missed OUTPUT in your test
greekius wrote: declare @Value nchar
EXEC [sp_GetConfigurationParameter] 'DBNL','DEVE',@Value
select @Value
Should be
declare @Value nchar
EXEC [sp_GetConfigurationParameter] 'DBNL','DEVE',@Value <big>OUTPUT</big>
select @Value
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hello,
I have the following query where I want to append to the existing table SalePrice and the the third coilumn SalePrice = 199 which is wrong .
Can you correct it ?
Can you sugest me sites to improve my query writing.
SELECT timeperiod.timeperiod,product.product,
convert(int,199) as SalePrice
INTO SalePrice FROM timeperiod,product where Product='Shirt199Light'
Can you
|
|
|
|
|
Ok, so you didn't really like my answer last time. What you want then is a variation on it.
SELECT timeperiod.timeperiod,product.product,
cast(null as int) as SalePrice
INTO SalePrice FROM timeperiod,product where Product='Shirt199Light'
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hello,
Your last answer was very helpful and I think asking the variation was really silly on my part but from where did you get these 'cast' and 'convert' 'as' keywords.
I have visited SQL sites but they don't offer anyting more than the basic SELEC,INSERT,WHERE keywords.
I will search for more sites and hope not to bother you much.
Thanks for your answer.
Pritha
|
|
|
|
|
Hello,
The above query does not add record to the existing table.It says
'There is already an object named 'SalePrice' in the database.'
I want to add records to an existing table where all the 3 columns exist.
Thanks.
Pritha
|
|
|
|
|
Hello,
I have got how to insert record
But are there any sites that offer help with such mix and match in SQL.
Prithaa
|
|
|
|
|
Hi all,
I want to connect to DB2 database in my application. IS there any command line utility for the same or is there any way I may program it. My application is based on C#.net.
Thanks,
Sankalp
|
|
|
|
|
|
hello friends
I want to convert crystal report to txt format * not rtf
Inner properties do support rtf,pdf and other formats but not txt
Thanks
Krazy Programmer
If You win You need not Explain............
But If You Loose You Should not be there to Explain......
|
|
|
|
|
I need to update same table in 3 different databases. All database Name contains word user. I want to write sql query update those tables. I am using sql server 2000. please help me this is very agent
ssss
|
|
|
|
|
If its a one off:
begin tran
update database1.dbo.table set .....
update database2.dbo.table set .....
update database3.dbo.table set .....
commit tran
You need to put some error checks and rollback, but you get the idea. If its an ongoing requirement you ought to consider replication
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Actually I don't no the exactly name of the database. I only know the database name contains the word "User". Plese can u help me
ssss
|
|
|
|
|
You need to generate some dynamic sql then. You can the update statements from master..sysdatabases
select 'update ' + name + '.dbo.table set ...' from master..sysdatabases
where name like '%user'
All you need to do then is put it in loop and execute it, or something similar - there are loads of ways from this point.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
Hello,
I have this following query
SELECT timeperiod.timeperiod,product.product,JobWork.JobWork
INTO DD FROM timeperiod,product,JobWork where Product='Shirt199Light'
But now I want to add one more column to table DD .
Is this possible in one query? Or Will I have to write one more statement after SELECT.
Thanks
Pritha
|
|
|
|
|
Yes.
SELECT timeperiod.timeperiod,product.product,JobWork.JobWork, convert(int,0) as NewCol <br />
INTO DD FROM timeperiod,product,JobWork where Product='Shirt199Light'
I have used int as an example, you can use anything - I often use a real column from the select taht is of the right data type, for example:
SELECT timeperiod.timeperiod,product.product,JobWork.JobWork, product.product as OtherColumn INTO DD FROM timeperiod,product,JobWork where Product='Shirt199Light'
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
I'm having difficulty identifying the best way to process a dataset I've been provided. It's a fairly large dataset (1.76m records) with two columns (pk1,pk2) in the primary key. e.g., {{1,a},{1,b},{1,c},{1,d},{2,a},{2,b},{3,a},{4,a},{4,b}}
What I need to do is read all the records for pk1 in the dataset {{1,1},{1,2},{1,3},{1,4}}, run some calculations on these, write the results to a different table, then move on to the next pk1. I've tried a couple methods, and run into problems in each. I'm wondering if anyone has any ideas for this.
I'm restricted using the local file system, and cannot install anything new locally: e.g., MS Access, text files (connecting through system.data.oledb), cannot install MySQL or SQLServer.
What I've done thus far, is:
- create a DataTable containing a list of distinct pk1 values [MainList]. {1,2,3,4}
- For each unique value in [MainList],
- Execute a sproc via OleDbDataAdapter.Fill method to populate a DataTable containing all records in the original dataset associated with the current pk1 {{1,a},{1,b},{1,c},{1,d}}
- Process this datatable to create the user's output
- Write the results to a separate set of tables in the same database.
This method takes an unacceptable amount of time, with 98% taken up by the .Fill method.
Next, I tried the same process, but using the OleDbDataReader in lieu of the .Fill(DataTable) process. This improves the speed slightly, but still not where we need it.
Running this file through sequentially, using a text file, and checking the current records key against the prior records key, we processed the entire file in about an hour.
We are currently running VB.NET 2005 with ADO.NET 2.0.
Also, are there any other options on databases available, provided we're not allowed to install something locally, and can only use the local system?
Any input would be appreciated.
|
|
|
|
|
Seems to me that it is a simple set of select statements, unless I am missing something.
A few questions come to mind.
Do you want to do this in the database or the client (VB)?
Do you need to store the results or just output them to the client?
Personally I would use a stored proc, this gives you many tools such as table vars, cursors etc.
1.76m records is not that large.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|