|
I've been trying to consolidate/reuse some business logic in a recent project and doing so would require me (within a MS-SQL 2000 stored procedure) to get a dataset from a stored procedure and aggregate some data. Is there a way to get a dataset from a stored procedure and put it in a temporary table? This is not an output variable, just the regular output dataset. I'm using Microsoft SQL 2000.
Help please!
USE PD9
DECLARE @RC int
DECLARE @datebegin varchar(10)
DECLARE @dateend varchar(10)
SET @datebegin = '01/01/2007'
SET @dateend = '01/15/2007'
--this is a dataset of our sales reps
DECLARE sales_cursor CURSOR
FOR
SELECT DISTINCT
[ABAN8]
FROM
[vF0101_RepDetails]
ORDER BY [ABAN8]
OPEN sales_cursor
DECLARE @an8 int
FETCH NEXT FROM sales_cursor INTO @an8
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
-- i'm looping through a dataset of our sales reps
-- now i need to get a dataset from the below sproc
-- loop through that dataset and put it in a temp table
-- the problem is getting the dataset from the next line - HOW??
-- EXEC @RC = [PD9].[dbo].[usp_rs_getSalesShipments] @an8, @datebegin, @dateend
-- print @RC
END
FETCH NEXT FROM sales_cursor INTO @an8
END
CLOSE sales_cursor
DEALLOCATE sales_cursor
|
|
|
|
|
You can use:
INSERT INTO #MyTempTable (col1, col2, col3, ..., coln)
EXEC MyStoredProc where the temporary table's column list matches the columns returned in the stored procedures resultset (and their data types).
|
|
|
|
|
hi
plzz tell me, if any one use split function in sql server 2000
i want to split one column data and then get coresponding values from another table like:-
table_1
year code
2001 1,2,6
2002 2,5,6
table_2
code name
1 one
2 two
5 five
6 six
i want to split code on(,)but this query is not working.
"select name from table_2 where code=(split(select code from table_1 where year='2001'))"
plzz help me
thanks in advance
john
-- modified at 11:16 Wednesday 7th March, 2007
|
|
|
|
|
|
Hi,
I know how to retrieve data from SQL to Dataset. Can anyone help me on how to insert updated data from Dataset back to SQL.
Thanks
Raj
|
|
|
|
|
From VS 2005 documentation:
DataAdapter.Update - Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable named "Table."
|
|
|
|
|
Thanks for your reply. "DataAdapter.Update" is for updating a DataTable in DataSet. I want to know how to insert this updated data back to SQL.
|
|
|
|
|
Try this snippet and let me know if it worked for you.
SqlConnection c = new SqlConnection(@"Data Source=(local)\SQL2005;Initial Catalog=TEST_DB;Integrated Security=True");<br />
SqlDataAdapter da = new SqlDataAdapter("select * from customer where customer_id = 1", c);<br />
SqlCommandBuilder cb = new SqlCommandBuilder(da);<br />
DataTable dt = new DataTable();<br />
<br />
da.Fill(dt);<br />
dt.Rows[0]["NAME"] = dt.Rows[0]["NAME"].ToString() + "1";<br />
da.Update(dt);
|
|
|
|
|
hi U have to use commandbuilder(dataadapterobject)
|
|
|
|
|
Hi
I have problem in getting primary and foreign key for a given table.
For example in a Adventureworks Database, Production.ProductCostHistory table.
If i try to get the foreign key related table it is not returning any value.
But ProductID is is both primary and foreign key.
How to get the foreign key table for the given table.
sp_fkeys not working if it is a composite key.
exec sp_fkeys @PKTable_name='ProductCostHistory',@PKTable_Owner='Production'
Can any one help me please?........
kesavan
|
|
|
|
|
If you have the correct rights you can select your table from sysobjects where [name] = 'yourtablename'
You can use the id from that table to look in sysindexes for your primary keys.
Then you can look in sysforeignkeys to see if you have some foreignkeys
Hope that helps.
Ben
|
|
|
|
|
can you give me any sample example for a column, it is both primary and foreign key please.
kesavan
|
|
|
|
|
It is not that easy.
First if you look in the sysobjects table for your table name to get the id.
Next if you look in the sysobjects table for an xtype = 'PK' that is a primary key that has a parent_obj = the id you found above. This gives you your primary key
Next you look in the sysforeignkeys table using the parentid
joined back to sysobjects to try and find a foreignkey if it exists.
So if you have a customer table and a customertype table
Customer Table:
customerId
CustomertypeId
... other stuff
CustomerType Table:
customertypeId
... other stuff
Here's a query:
declare @id int
select @id = id from sysobjects where [name] = 'customertype'
select * from sysobjects where xtype = 'pk' and parent_obj = @id
select * from sysforeignkeys fk
join sysobjects so on fk.fkeyid = so.parent_obj and so.xtype = 'f'
where fk.rkeyid = @id
I don't know if that helps at all it is quite complicated.
Ben
|
|
|
|
|
Thanks a lot ben
I have done this by following way
Select Tab1.Column_name,(select Schema_name(uid) from sys.sysobjects where id=PK_table_id)+'.'+Tab2.PKtable_name as PKTable_name, Tab2.PKColumn_name, Tab1.Table_Schema+'.'+Tab2.FKTable_Name as FKTable_Name, Tab2.FKColumn_Name from (Select Column_Name, Data_Type, Table_Schema from Information_schema.columns where Table_Name='Product' and Table_Schema='Production') Tab1 left outer join (select PKColumn_Name=c1.name,PKTable_Name=Convert(Sysname,Object_Name(f.refereced_Object_id)),PKTable_ID=f.referenced_Object_id,FKTable_Name=o1.name,FKColumn_Name=c2.name from sys.all_objects o1, sys.all_objects o2, sys.all_columns c1, sys.all_column c2, sys.foreign_keys f inner join sys.foreign_key_column k on(k.constraint_object_id=f.object_id) inner join sys.indexes i on (f.referenced_object_id=i.object_id and f.key_index_id=i.index_id) where o1.object_id=f.parent_object_id and o1.object_id=object_id('production.product') and o2.object_id=f.parent_object_id and c1.object_id=f.referenced_object_id and c2.object_id=f.parent_object_id and c1.column_id=k.referenced_column_id and c2.column_id=k.parent_column_id) Tab2 on Tab1.Column_name=Tab2.FKColumn_name
kesavan
|
|
|
|
|
Hi!
How can one get a list of all the tables in an Access database using a query? ...I'm interested in something similar in effect to the SHOW TABLES in MySql.
Thanks.
|
|
|
|
|
|
I 've got a textbox which has to accept only numeric values..do i use the regularexpression validator(if so how?)
karabo mekgwe
Ecom Institute
|
|
|
|
|
wrote: do i use the regularexpression validator(if so how?)
You can do. Off the top of my head the regular expression would be something like
^[0-9]*$
NOTE: This accepts only positive integers only.
|
|
|
|
|
Dear All,
i am making small web application using asp.net, C# ,sql2000.
i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server
Please help
thanks
regards
imran khan
|
|
|
|
|
Dear Mr. Imran Khan,
Let me describe your problem. You have two sql server. You want to query from these two server. If this is your problem. then you can use Linked Server.
You will get more information about this in
http://www.sqlteam.com[^]
Mehedi Hasan
|
|
|
|
|
THanks for quick reply.
but your link only go to the site does not show topic.
please guide me.
regards
imran khan
|
|
|
|
|
|
I have a column in my database called SaleNumber which has a datatype numeric(18,0). One of the values I have stored in there is 11101930593 however when I retrieve this value from the database in my delphi application the number displayed is -1782971295.
Does anybody know what could possibly be going on here?
|
|
|
|
|
I don't know Delphi so this could be way off the mark. I'm guessing that it might be an overflow. Are you putting the column value in a 32bit integer? A 32bit integer has a range of rougly -2billion to +2billion. Your number is roughly 11 billion.
|
|
|
|
|
No I am parsing it in as an unsigned long.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
