Data File Remaining Space

I am running SQL 2000. I have a data file set up with restricted growth. Is
there a stored procedure or script available that I can schedule or run
when I want manually that will give me the space remaining in the data file?
Message posted via http://www.sqlmonster.com
Checkout sp_spaceused in BOL.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.c om...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.com
|||sp_helpdb YourDBNameHere
gives some information regarding database and file size.
Keith
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.c om...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.com

Data File Remaining Space

I am running SQL 2000. I have a data file set up with restricted growth. Is
there a stored procedure or script available that I can schedule or run
when I want manually that will give me the space remaining in the data file?
Message posted via http://www.droptable.comCheckout sp_spaceused in BOL.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQ
droptable.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.droptable.com|||sp_helpdb YourDBNameHere
gives some information regarding database and file size.
Keith
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQ
droptable.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.droptable.com

Data File Remaining Space

I am running SQL 2000. I have a data file set up with restricted growth. Is
there a stored procedure or script available that I can schedule or run
when I want manually that will give me the space remaining in the data file?
--
Message posted via http://www.sqlmonster.comCheckout sp_spaceused in BOL.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.com|||sp_helpdb YourDBNameHere
gives some information regarding database and file size.
--
Keith
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.comsql

Data file inlezen via Stored Procedure

Hello
I want to import a datafile into an sql database with stored procedure. But i have now idee how i must do that.
Also the procedure must be run automatily each morning at 10 a clock.
Greetz TomCreate stored procedure for importing data and setup job for running this procedure.

Data Driven

Hi,
I understand I can create a stored procedure to return all the parameters
for data driven. The last statement of the Stored Procedure is Select *
DataDrivenTableName. However, after I put Exec SPName, and when i go to the
next page to fill out all the To, CC, Subject..., there is nothing in each
drop down box and I can't continue to finish the setup.
Any idea what's wrong.
Thanks
EdAFAIK RS uses first resultset to get data, not last.
Stjepan
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2F0D6FE4-2FA8-49EB-A965-D0A57D47362C@.microsoft.com...
> Hi,
> I understand I can create a stored procedure to return all the parameters
> for data driven. The last statement of the Stored Procedure is Select *
> DataDrivenTableName. However, after I put Exec SPName, and when i go to
> the
> next page to fill out all the To, CC, Subject..., there is nothing in each
> drop down box and I can't continue to finish the setup.
> Any idea what's wrong.
> Thanks
> Ed

Data display in table

Hi,

I have a result set from a store procedure which looks like this.

Column1 Column2 Column3

A 1 lit1

A 1 Rep2

A 1 Reg3

B 2 ram1

B 2 lim2

I want to show data in my table like this:

Column1 Column2 Column3

A 1 lit1; Rep2;Reg3

B 2 ram1;lim2

Can anyone tell me how I can do this?

Thanks

Ashwini

Hi,

While getting the result from the database you can do one thing i.e write a Scalar valued function which will take the Column 1 value in that stored procedure write the logic to get the Column3 values separated by semi colon like this

ALTER FUNCTION [dbo].[GetColumn3]

(

@.Column1 bigint //here datatype of your column

)

RETURNS varchar(max)

AS

BEGIN

DECLARE @.Result varchar(max)

SET @.Result = ''

--Select all Column3 into a string seperated by ';' for a column1

SELECT @.Result = @.Result + CONVERT(VARCHAR(MAX), p.Column3) + ', '

from //your table table1

WHERE

AND table1.(Your Column name here) = @.Column1

//This code to remove your last semi colon

IF @.Result != ''

SELECT @.Result= SUBSTRING(@.Result , 1, LEN(@.Result )-1)

RETURN @.Result

END

and call that function in your main Select query:

Select Column1,Column2 , dbo.GetColumn3(Column1) as Column3

From table1

joins

Where Conditions

Hope this helps.

sql

Data Device too big Sql6.5

I receive a Backupdevice size=30 Mbytes as full database backup

i create the database with "fromload option"

the execute the restore procedure send the error because the original databse device size is 2500 Mbytes but the database is not too big

who i can restore the database with a samller database device?The database has to be restored with files the same size as the database that was backed up.|||Certainly but this file has a size 1000 times bigger than the needed size and it does not fit in my server.

The question its: if exist some procedure to restore the database in a Database device smaller, but sufficiently.|||its impossible?|||Use the following command to get some information on the original database:

load headeronly from (device)

This may give you some ideas on how the original was laid out. Also, pay attention to the details of the error you get back from the load database command. This is where it should tell you how big and how many devices there were on the original.|||On 6.5 there is a procedure called sp_help_revedatabase. Unlike 2000, on SQL 6.5 you need to have an existing database, that has been built the same way as the original database. Run the procedure on the original server, and take its output, paste it in a query window to create the new database on the new server... After which, the restore should be a snap. This is off the top of my head, so you may want to look it up on BOL, or a search engine for the exact process.

This is assuming you have access to the original server that the database was dumped. If not, sorry.|||thanks ill try it

Data corrupted after server crash ?

Our SQL Server 2000 crashed a while a go and i am wondering if this
could cause
data to be corrupted ?
Could someone tell me what the procedure of SQL Server 2000 is after
and or during a server crash ?
Dennis Velders
Dennis
If you do an access to the database , just run BACKUP LOG WITH NOTRUNCATE
option
Then RESTORE the last FULL database and apply all LOGs with NORECOVERY
option except the last one that you did just after the crash but this time
WITH RECOVERY option
For more details please refer to the BOL
"Dennis Velders" <d.velders@.catalpa.nl> wrote in message
news:1141288312.167931.162580@.z34g2000cwc.googlegr oups.com...
> Our SQL Server 2000 crashed a while a go and i am wondering if this
> could cause
> data to be corrupted ?
> Could someone tell me what the procedure of SQL Server 2000 is after
> and or during a server crash ?
>
> Dennis Velders
>
|||Hi
This will depend on what damage has been caused by a crash. SQL Server will
roll back any outstanding transactions when it starts, therefore you database
should be consistent if it comes back online.
If your database does not come online it will depend on what is
corrupted/missing as to what you can recover. The worst case being that you
will have to resort to a backup.
Have you run a DBCC CHECKDB on the database?
John
"Dennis Velders" wrote:

> Our SQL Server 2000 crashed a while a go and i am wondering if this
> could cause
> data to be corrupted ?
> Could someone tell me what the procedure of SQL Server 2000 is after
> and or during a server crash ?
>
> Dennis Velders
>
|||Yes, i did all the checks.
But after a normal shutdown a while ago the data was fine.
So, i have two options for the maybe corrupted data:
1. The Query mechanism of SQL Server is wrong after the crash
2. The Views display the wrong data, vew design problem
Someone is blaming the server for the corruption...
I want to be sure.
|||Hi
If you run checkdb and it indicates not errors, then you may also want to
reindex and update the statistics. If it continues to give different results,
then try running the query in Query analyser and verify that the results are
matching the query and the underlying data.
John
"Dennis Velders" wrote:

> Yes, i did all the checks.
> But after a normal shutdown a while ago the data was fine.
> So, i have two options for the maybe corrupted data:
> 1. The Query mechanism of SQL Server is wrong after the crash
> 2. The Views display the wrong data, vew design problem
> Someone is blaming the server for the corruption...
> I want to be sure.
>

Data corrupted after server crash ?

Our SQL Server 2000 crashed a while a go and i am wondering if this
could cause
data to be corrupted ?
Could someone tell me what the procedure of SQL Server 2000 is after
and or during a server crash ?
Dennis VeldersDennis
If you do an access to the database , just run BACKUP LOG WITH NOTRUNCATE
option
Then RESTORE the last FULL database and apply all LOGs with NORECOVERY
option except the last one that you did just after the crash but this time
WITH RECOVERY option
For more details please refer to the BOL
"Dennis Velders" <d.velders@.catalpa.nl> wrote in message
news:1141288312.167931.162580@.z34g2000cwc.googlegroups.com...
> Our SQL Server 2000 crashed a while a go and i am wondering if this
> could cause
> data to be corrupted ?
> Could someone tell me what the procedure of SQL Server 2000 is after
> and or during a server crash ?
>
> Dennis Velders
>|||Hi
This will depend on what damage has been caused by a crash. SQL Server will
roll back any outstanding transactions when it starts, therefore you database
should be consistent if it comes back online.
If your database does not come online it will depend on what is
corrupted/missing as to what you can recover. The worst case being that you
will have to resort to a backup.
Have you run a DBCC CHECKDB on the database?
John
"Dennis Velders" wrote:
> Our SQL Server 2000 crashed a while a go and i am wondering if this
> could cause
> data to be corrupted ?
> Could someone tell me what the procedure of SQL Server 2000 is after
> and or during a server crash ?
>
> Dennis Velders
>|||Yes, i did all the checks.
But after a normal shutdown a while ago the data was fine.
So, i have two options for the maybe corrupted data:
1. The Query mechanism of SQL Server is wrong after the crash
2. The Views display the wrong data, vew design problem
Someone is blaming the server for the corruption...
I want to be sure.|||Hi
If you run checkdb and it indicates not errors, then you may also want to
reindex and update the statistics. If it continues to give different results,
then try running the query in Query analyser and verify that the results are
matching the query and the underlying data.
John
"Dennis Velders" wrote:
> Yes, i did all the checks.
> But after a normal shutdown a while ago the data was fine.
> So, i have two options for the maybe corrupted data:
> 1. The Query mechanism of SQL Server is wrong after the crash
> 2. The Views display the wrong data, vew design problem
> Someone is blaming the server for the corruption...
> I want to be sure.
>

Data corrupted after server crash ?

Our SQL Server 2000 crashed a while a go and i am wondering if this
could cause
data to be corrupted ?
Could someone tell me what the procedure of SQL Server 2000 is after
and or during a server crash ?
Dennis VeldersDennis
If you do an access to the database , just run BACKUP LOG WITH NOTRUNCATE
option
Then RESTORE the last FULL database and apply all LOGs with NORECOVERY
option except the last one that you did just after the crash but this time
WITH RECOVERY option
For more details please refer to the BOL
"Dennis Velders" <d.velders@.catalpa.nl> wrote in message
news:1141288312.167931.162580@.z34g2000cwc.googlegroups.com...
> Our SQL Server 2000 crashed a while a go and i am wondering if this
> could cause
> data to be corrupted ?
> Could someone tell me what the procedure of SQL Server 2000 is after
> and or during a server crash ?
>
> Dennis Velders
>|||Hi
This will depend on what damage has been caused by a crash. SQL Server will
roll back any outstanding transactions when it starts, therefore you databas
e
should be consistent if it comes back online.
If your database does not come online it will depend on what is
corrupted/missing as to what you can recover. The worst case being that you
will have to resort to a backup.
Have you run a DBCC CHECKDB on the database?
John
"Dennis Velders" wrote:

> Our SQL Server 2000 crashed a while a go and i am wondering if this
> could cause
> data to be corrupted ?
> Could someone tell me what the procedure of SQL Server 2000 is after
> and or during a server crash ?
>
> Dennis Velders
>|||Yes, i did all the checks.
But after a normal shutdown a while ago the data was fine.
So, i have two options for the maybe corrupted data:
1. The Query mechanism of SQL Server is wrong after the crash
2. The Views display the wrong data, vew design problem
Someone is blaming the server for the corruption...
I want to be sure.|||Hi
If you run checkdb and it indicates not errors, then you may also want to
reindex and update the statistics. If it continues to give different results
,
then try running the query in Query analyser and verify that the results are
matching the query and the underlying data.
John
"Dennis Velders" wrote:

> Yes, i did all the checks.
> But after a normal shutdown a while ago the data was fine.
> So, i have two options for the maybe corrupted data:
> 1. The Query mechanism of SQL Server is wrong after the crash
> 2. The Views display the wrong data, vew design problem
> Someone is blaming the server for the corruption...
> I want to be sure.
>

data convertion with dynamic sql

Hi, All,
I;m new to SQl server and having a problem with dynamic sql within an
procedure.
I got error(the error msg and procedure are below) when passing an smallint
value (@.batch_ID)to the dynamic sql, do I need to conver it to didferent
datatype, how to do it?
THanks a lot.
Here is the error message I got:
**********
0
ST_ccst_code_value
Msg 245, Level 16, State 1, Procedure CR_Update_Stage_tables, Line 23
Conversion failed when converting the nvarchar value 'Update
ST_ccst_code_value Set DTS_BATCH_ID =' to data type smallint.
Here is the procedure:
****
ALTER PROCEDURE [dbo].[CR_Update_Stage_tables]
@.BatchName nvarchar(30)
AS
Declare @.batch_ID smallint
select @.batch_ID = 0
Declare @.SQL VarChar(1000)
Declare @.ST_table nvarchar(30)
exec SP_CR_Get_Batch_ID @.BatchName, @.batch_ID output
print @.batch_ID
select @.ST_table = 'ST_'+ @.BatchName
print @.ST_table
SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ @.batch_ID
Exec (@.SQL)
GOWhen you try to add (concatenate) the value for @.batch_ID to the query
string, SQL tries to implicitly convert the string to an integer, because in
t
has a higher data type precedence than char data types.
Try:
SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ CAST(@.batch_ID
as nvarchar(10))
Exec (@.SQL)
"Jessie" wrote:

> Hi, All,
> I;m new to SQl server and having a problem with dynamic sql within an
> procedure.
> I got error(the error msg and procedure are below) when passing an smallin
t
> value (@.batch_ID)to the dynamic sql, do I need to conver it to didferent
> datatype, how to do it?
> THanks a lot.
> Here is the error message I got:
> **********
> 0
> ST_ccst_code_value
> Msg 245, Level 16, State 1, Procedure CR_Update_Stage_tables, Line 23
> Conversion failed when converting the nvarchar value 'Update
> ST_ccst_code_value Set DTS_BATCH_ID =' to data type smallint.
> Here is the procedure:
> ****
> ALTER PROCEDURE [dbo].[CR_Update_Stage_tables]
> @.BatchName nvarchar(30)
> AS
> Declare @.batch_ID smallint
> select @.batch_ID = 0
> Declare @.SQL VarChar(1000)
> Declare @.ST_table nvarchar(30)
>
> exec SP_CR_Get_Batch_ID @.BatchName, @.batch_ID output
> print @.batch_ID
> select @.ST_table = 'ST_'+ @.BatchName
> print @.ST_table
> SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ @.batch_ID
> Exec (@.SQL)
> GO

Data Convertion Error

I am using SQL server 2005, Visual Web Developer 2005 express (for right now). Can get the stored procedure to run fine if I do not return the CityID.

Stored Procedure

ALTER Procedure [dbo].[WDR_CityAdd1]

(

@.CountryID int,

@.CityName nvarchar(50),

@.InternetAvail bit,

@.FlowersAvail bit,

@.CityID int OUTPUT

)

AS

IF EXISTS(SELECT 'True' FROM city WHERE CityName = @.CityName AND CountryID = @.CountryID)

BEGIN

SELECT

@.CityID = 0

END

ELSE

BEGIN

INSERT INTO City

(

CountryID,

CityName,

InternetAvail,

FlowersAvail

)

VALUES

(

@.CountryID,

@.CityName,

@.InternetAvail,

@.FlowersAvail

)

SELECT

@.CityID = 'CityID' ( I have also tried = @.@.Identity but that never returned anything it is an identity column 1,1)

END

Here is the code on the other end. I have not included all the parameters, but should get a sense of what I am doing wrong.

Dim myCommand As New SqlCommand("WDR_CityAdd1", dbConn)

myCommand.CommandType = CommandType.StoredProcedure

Dim parameterCityName As New SqlParameter("@.CityName", SqlDbType.NVarChar, 50)

parameterCityName.Value = CityName

myCommand.Parameters.Add(parameterCityName)

Dim parameterCityID As New SqlParameter("@.CityID", SqlDbType.Int, 4)

parameterCityID.Direction = ParameterDirection.Output

myCommand.Parameters.Add(parameterCityID)

Try

dbConn.Open()

myCommand.ExecuteNonQuery()

dbConn.Close()

Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)

'AlertMessage = "City Added"

Catch ex As Exception

AlertMessage = ex.ToString

End Try

Here is the error I get. So what am I doing wrong? I figured maybe in the stored procedure. CityID is difined in the table as an int So why is it telling me that it is a varchar when it is defined in the stored procedure, table and code as an int? Am I missing something?

System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'CityID' to data type int. at System.Data.SqlClient.SqlConnection.OnError

Thanks

Jerry

There are a couple of issues:

grbourque wrote:

...

SELECT @.CityID = 'CityID'

...

Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)

...

--> Conversion failed when converting the varchar value 'CityID' to data type int.

In this location,

SELECT @.CityID = 'CityID'

You are attempting to assign the @.CityID (an integer) the string value 'CityID'. That is what caused the error you reported.

I suggest using SCOPE_IDENTITY to capture the IDENTITY value of the last row entered by the current user/session.

SET @.CityID = SCOPE_IDENTITY()

Also, the RETURN value 'should' be an integer, and you are attempting to return a string value.

('Normally', one would use the RETURN value for success/failure reporting.)

Somewhere prior to the RETURN statement, you 'should' assign the parameterCityID value to a previously declared variable, then use that variable in your application.

|||

Arnie

I had tried playing around with the 'cityID' fieldname and was getting different error messages. I have re-written the code with your help and it works. Sometimes this just amazes me. I do appreciate the help. Maybe with time I will be able to help others as I get a handle on this.

Jerry

Data binding to a stored procedure that returns two result sets

Hi there everyone. I have a stored procedure called "PagingTable" that I use for performing searches and specifying how many results to show per 'page' and which page I want to see. This allows me to do my paging on the server-side (the database tier) and only the results that actually get shown on the webpage fly across from my database server to my web server. The code might look something like this:

strSQL = "EXECUTE PagingTable " & _

"@.ItemsPerPage = 10, " & _

"@.CurrentPage = " &CStr(intCurrentPage) &", " & _

"@.TableName = 'Products', " & _

"@.UniqueColumn = 'ItemNumber', " & _

"@.Columns = 'ItemNumber, Description, ListPrice, QtyOnHand', " & _

"@.WhereClause = '" & strSQLWhere &"'"

The problem is the stored procedure actually returns two result sets. The first result set contains information regarding the total number of results founds, the number of pages and the current page. The second result set contains the data to be shown (the columns specified). In 'classic' ASP I did this like this.

'Open the recordset

rsItems.Open strSQL, conn, 0, 1

'Get the values required for drawing the paging table

intCurrentPage = rsItems.Fields("CurrentPage").Value

intTotalPages = rsItems.Fields("TotalPages").Value

intTotalRows = rsItems.Fields("TotalRows").Value

'Advance to the next recordset

Set rsItems = rsItems.NextRecordset

I am trying to do this now in ASP.NET 2.0 using the datasource control and the repeater control. Any idea how I can accomplish two things:

A) Bind the repeater control to the second resultset

B) Build a "pager" of some sort using the values from the first resultset

Hi,

have you tried setting the DataMember property of the repeater ?

|||

Thanks for the quick reply.

I was thinking about that but I don't know what the second result set would be "called". The results come back as if you executed two SELECT statements one after another without a UNION statement in between.

So what would I set the repeater's DataMember property to? Can I use an integer like

repItems.DataMember = 1 (assuming 0 is the first result set)

|||

If you don't specify a name...:

Table1

Table2

...

TableN

Data access From sqlserver 2000

Hello

i have one prob . i m using sql server 2000 . and i have write a store procedure to fetch data from multiple table using cursor . this query exculate in sql server . but i can't fetch this data from in our page . how to fetch data from using multiple table .plz help

thnx

Hi shruram.gore,

You don't need to use cursor for this unless you need to do some operations (processess) before displaying the data.
Cursor will not help you in displying the data, to display the data use SELECT and if you need to fetch the data from more than one table, use JOIN.

Example:

SELECT a.col1, a.col2, b.col2FROM table1 aINNERJOIN table2 bon (a.id = b.id)

Good luck.

|||

thnx , for suggestion

But dont have fix table name . in my prob my table name is variable plz check the query . this query we will use to bind treeView to show parent child releation . i have execute data from this query but we cant fetch data in dataset

thanx in advance

CREATE PROCEDURE [dbo].GetRootChild
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
declare @.parentId int
declare @.TableName varchar (50)
declare @.RelIDName char(10),@.s int,@.sql varchar(4000)

declare comments_list CURSOR FOR
select distinct [PcRelation].[ChID],[ObjectType].[ObjName],[ObjectType].[RelIdName] from PcRelation,ObjectType where [PcRelation].[ChObjectID]=[ObjectType].[ID] and [PcRelation].[PID]='0' and [PcRelation].[PObjectID]='0'
OPEN comments_list
FETCH NEXT FROM comments_list INTO @.parentId,@.TableName,@.RelIDName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE product_cursor CURSOR FOR
select @.TableName as TableName
--select * from Company
select @.sql ='select * from [dbo].['+ @.TableName +'] where ['+ @.TableName +'].['+@.RelIDName+']= '+convert(char(10),@.parentId)+''
OPEN product_cursor
FETCH NEXT FROM product_cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
--select @.TableName as TableName
exec(@.sql)
FETCH NEXT FROM product_cursor
END
CLOSE product_cursor
DEALLOCATE product_cursor
FETCH NEXT FROM comments_list INTO @.parentId,@.TableName,@.RelIDName
END
CLOSE comments_list
DEALLOCATE comments_list
GO

and fetch data from sql

if cant image refer this url

http://shriramgore.ifastnet.com/?q=node/41

thnx

|||

Hi,

Based on my understing, since you didn't do any row-based operations on the table(after you have fetched each row, seems you didn't perform any operations), there is no need for you to use cursors. If what you want is just to fetch data from multi tables, a simpleJoin will work in your case. Use dynamic query if you cannot determine your table name until run-time. Like:

declare @.str nvarchar(100) ,@.str1 varchar(100)set @.str='table_1'set @.str1='table_2'exec ('select * from '+@.str+' as a inner join '+ @.str1+' as b on a.id=b.id')

Hope my suggestion helps|||

thnx for replyBo Chen

but i have following like ......... please check example ...

I have data from multiple table, those table's Names are store as Variable in a table . in this Case i have written 1 store procedure using Cursor . this query Execute in server like (Please check this Url -http://shriramgore.ifastnet.com/?q=node/41&size=_original ) . after executing storeprocedure i get data in different tabels this data i can't fetch in single dataset .if i will fetch all tables(results of storeprocedure) then it eaiser to me for next part .

e.g
DataSet ds = new DataSet();
ds = proc.GetDataSet();

for (int i= 0; i< ds.Tables.Count; i++)
{
for (int y= 0; y< ds.Tables [i].Rows.Count; y++)
{
String str=ds.tabels[i].rows[y]["ColumnName"].toString();
}
}

in this example i enable to get other tabels (dsParent1.Tables[int] ).

please help

thnx in advance

|||

Hi shriram.gore,

I can see your problem. In my opinion, i think you can try to revise your stored procedure, like this: pass three or four parameters(as you need) into your stored procedure and make them as output paramters. In your stored procedure, you can assign them the table-name values during sql clause execution. For example:

create procedure sp_get_tbl_name (@.str1 varchar(100) output,
@.str2 varchar(100) output, @.str3 varchar(100) output...)
as
select @.str1=tablename
from yourtablename
where .....
go
select @.str2=tablename
from yoursecondtablename
where...
go
..

Hope my suggestion helps

|||

Hello ,

thnx for Reply

Yes uropinion is correct . That query iwould like to use in for loop to fetch data from multiple table . m i rt . in my case i can't use for loop in my page bcoz we have 200 record's from 5 table (Varible table - those declare in other table).if i had use for loop then those query fire 200 time . m i rt .

excuse i m not using sqlserver 2005

thnx

shriram

|||

Yes uropinion is correct . That query iwould like to use in for loop to fetch data from multiple table . m i rt . in my case i can't use for loop in my page bcoz we have 200 record's from 5 table (Varible table - those declare in other table).if i had use for loop then those query fire 200 time . m i rt .

If i understand you correct, that's not a problem.

Since now you have got those table names, you can user your sldataadpater to perform a sql data retrieval against each data table name--after that you will get server datasets, say, ds1, ds2,ds3,ds4..etc. Generally speaking each dataset you get contains only one table(it depends on your select command)

Then you can create a new dataset named ds, run : ds.tables.add(ds1.table[0]); ds.tables.add(ds2.table[0]),ds.tables.add(ds3.table[0])...etc..

Now your dataset ds contains all the tables you need. You can perform your loop on it.

BTW, what does "m i rt. " stand for??

thanks

|||

HelloBo Chen ,

thank u very much . my prob is solved .

thnx

shriram