Database

Re: Calculating the space occupied by each row in a table

6-Jul-07 11:13

Space means bytes or number of tuples which exists?

vijay25837-Jul-07 4:23

vijay2583

7-Jul-07 4:23

Hi

Thanks for reply . I want to calculate space in bytes.

Thanks
Bhaskar

Re: Calculating the space occupied by each row in a table

Vishu Gurav7-Jul-07 13:26

Re: Calculating the space occupied by each row in a table

7-Jul-07 13:26

Hai Buddy,
Cool u can do it as following;)

Find length of data stored in row of SQL Table

select datalength(sizeofrow) from tablename where i = 1

i=1 is kinda condition for fetching row...which u want....

best luk
vishu

Vishu Gurav
University of Nebraska-Lincoln

vijay25839-Jul-07 0:01

vijay2583

9-Jul-07 0:01

Hi,

Thanks for reply, But the query you have given is not working. It is giving Invalid column name 'sizeofrow'. Error

Thanks
vijay

Re: Calculating the space occupied by each row in a table

Vishu Gurav9-Jul-07 6:44

Re: Calculating the space occupied by each row in a table

9-Jul-07 6:44

Oh my apologies you need to have coulumn of that name I think.
Why dont you add one more coulmn name as sizeofrow with varchar(20) datatype.

this will work i believe if not please refer here:
http://blogs.msdn.com/sqltips/archive/2006/07/14/666188.aspx

Thanks,
Vishu Smile | :)

Vishu Gurav
University of Nebraska-Lincoln

Vishu Gurav10-Jul-07 17:00

Problem displaying colums as rows => need help

10-Jul-07 17:00

Did u get it wrked?

Vishu Gurav
University of Nebraska-Lincoln

masyarial5-Jul-07 16:09

masyarial

5-Jul-07 16:09

Hi, I have simple table1 like this:
name type value
-----------------------
aa A 0.1
aa B 0.2
aa C 0.3
bb A 0.4
bb B 0.5
bb C 0.6

Then, I doing the code from Krish
SELECT nama,
CASE WHEN test_type = 'A' THEN value ELSE 0 END AS 'A',
CASE WHEN test_type = 'B' THEN value ELSE 0 END AS 'B',
CASE WHEN test_type = 'C' THEN value ELSE 0 END AS 'C'
FROM table1

But the result is:
name A B C
--------------------------
aa 0.1 0 0
aa 0 0.2 0
aa 0 0 0.3
bb 0.4 0 0
bb 0 0.5 0
bb 0 0 0.6

But, I'm expecting table like this:
name A B C
--------------------------
aa 0.1 0.2 0.3
bb 0.4 0.5 0.6

Thanks a lot !!!

Re: Problem displaying colums as rows => revised

masyarial5-Jul-07 21:36

masyarial

5-Jul-07 21:36

Hi, I have simple table1 like this:
name type value
------------------
aa A 0.1
aa B 0.2
aa C 0.3
bb A 0.4
bb B 0.5
bb C 0.6

Then, I doing the code from Krish
SELECT nama,
CASE WHEN type = 'A' THEN value ELSE 0 END AS 'A',
CASE WHEN type = 'B' THEN value ELSE 0 END AS 'B',
CASE WHEN type = 'C' THEN value ELSE 0 END AS 'C'
FROM table1

But the result is:
name A B C
-------------------
aa 0.1 0 0
aa 0 0.2 0
aa 0 0 0.3
bb 0.4 0 0
bb 0 0.5 0
bb 0 0 0.6

But, I'm expecting table like this:
name A B C
-------------------
aa 0.1 0.2 0.3
bb 0.4 0.5 0.6

How a resolve this problem, need your help.
Thanks a lot !!!

SQL Server 2005 Changing Table Names

Richard Andrew x645-Jul-07 14:50

Richard Andrew x64

5-Jul-07 14:50

A client sent me a backup copy of his database so that I could debug a stored procedure.

After I restored the .bak file into my copy of SQL Server 2005, I see that it renamed every table by prepending the original name of the database to each table name.

So now every table must be referred to by "schema.tablename" instead of just "tablename". This breaks every stored procedure in the database because they all still refer to the tables by their old names.

Why does it insist on renaming the tables, and how do I get it to stop doing that whenever I restore a database from a .bak file?

--------------------------------

"All that is necessary for the forces of evil to win in the world is for enough good men to do nothing" -- Edmund Burke

Re: SQL Server 2005 Changing Table Names

originSH5-Jul-07 22:09

originSH

5-Jul-07 22:09

I've never seen it do that before ... I've always restored databases back to databases with the same name and it works fine.

Bind with an intermediate table

Richard Blythe5-Jul-07 10:24

Richard Blythe

5-Jul-07 10:24

I am in the proccess of writing a small membership program and all the info is being stored in an MS Access database. I've gotten stumped on coding the datagridview's "DataRelation" object because I'm having to code through three table levels.
To explain, I want to create families (Father, Mother, Children) and link them through the DataGridview.

I have a master table called "tblFamilyNames" that holds the "primary key" and anniversary date. I then have a table called tblFamilyDetails. It links the family(Father, Mother, Children) together by storing the primary key value from tblFamilyNames in a column called "intFamilyID".

In tblFamilyDetails, there is a column: "intMemberID" that points to a record in the third table: tblMembers".

My question is, how do I bind the first table: "tblFamilyDetails" with the third table: "tblMembers" so that the user can see the member's info without seeing the intermediate table: "tblFamilyDetails"?

may your code be error free

Re: Bind with an intermediate table

Humble Programmer6-Jul-07 6:26

Humble Programmer

6-Jul-07 6:26

This may not be the answer that you're expecting, but have you considered using Views to de-normalize the data? It sounds like you could create a "Family" view that is an INNER JOIN of tblFamilyNames and tblFamilyDetails. This could then be JOINed to tblMembers to give you a single unified view of the data.

Cheers!
Humble Programmer
,,,^..^,,,

ADO and MSACCESS [modified]

AmirAlilou5-Jul-07 2:39

5-Jul-07 2:39

Hi

I have a problem with INSERT,UPDATE table when working with ODBC in DotNet.

if you go to the following link and fill all the field and then click button see the error .i can not solve this.
http://www.bastanbar.com/ViewOrders.aspx
my connection string is in the following function

<br />
Public Shared Function GetConnection() As OdbcConnection<br />
        Dim myConn As OdbcConnection<br />
        Dim connStr, path As String<br />
        Dim Request As System.Web.HttpRequest<br />
        path = HttpContext.Current.Request.PhysicalApplicationPath()<br />
        path = path + "data.mdb"<br />
        connStr = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" + path + ";ReadOnly=0;"<br />
        myConn = New OdbcConnection(connStr)<br />
        Return myConn<br />
    End Function<br />

when i try to add one record to my table an error ocures
"ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query"

please help me to solve this problem

martin_hughes5-Jul-07 3:11

martin_hughes

5-Jul-07 3:11

What does your INSERT query look like?

AmirAlilou5-Jul-07 3:18

5-Jul-07 3:18

Hi martin

My INSERT query is in the following function

<br />
Public Function SaveTransInfo(ByVal idrequestman As Long, ByVal iddestinationInfo As Long, ByVal Y As String, ByVal M As String, ByVal D As String, ByVal Goods As String, ByVal idworker As Byte, ByVal Tonaj As Double, ByVal place As String, ByVal sdate As String) As Boolean<br />
        Dim sqlStr As String<br />
        Dim myCon As OdbcConnection = GetConnection()<br />
        sqlStr = "INSERT INTO TransInfo(idrequestman,iddestinationInfo,Y,M,D,Goods,idworker,Tonaj,place,sdate) VALUES(" + idrequestman.ToString & "," & iddestinationInfo & ",'" & Y & "','" & M & "','" & D & "','" & Goods & "'," & idworker.ToString & "," & Tonaj.ToString & ",'" & place & "','" & sdate & "');"<br />
        Try<br />
            Dim myCommand As New OdbcCommand(sqlStr, myCon)<br />
            myCon.Open()<br />
            myCommand.ExecuteNonQuery()<br />
        Catch ex As Exception<br />
            myCon.Close()<br />
            HttpContext.Current.Response.Write(ex.Message)<br />
            Return False<br />
        End Try<br />
        myCon.Close()<br />
        Return True<br />
    End Function<br />
<br />
thanks for your attention<br />
<br />

martin_hughes5-Jul-07 5:49

martin_hughes

5-Jul-07 5:49

You may need to adjust the permissions on the database file so that it can be written to by the ASP.Net user.

In Explorer navigate to the file and go to it's properties. Select security and add the built in ASPNET user and give it read/write permissions.

AmirAlilou5-Jul-07 6:16

5-Jul-07 6:16

Hi My files in my Host and i check read/write permissions .i have read/write permission to my db and the folder that my db reside in.(in fireFTP of mozilla i right click on my db and click property in the opend widow show me that i have read/write permissions to db)

i set

<br />
   authentication mode="Windows" <br />
   identity impersonate="true" <br />

BestRegards

martin_hughes5-Jul-07 7:00

martin_hughes

5-Jul-07 7:00

Hmmm... I'm not sure what the cause is really. If you hard code the entire SQL statement (just for testing problems, does the error still occur?

I've also found this, which might be useful:

http://support.microsoft.com/kb/175168[^]

AmirAlilou5-Jul-07 7:48