focus on row - Database Discussion Boards

Re: "show create table" equivalent in MSSQL

Colin Angus Mackay11-Dec-05 4:48

11-Dec-05 4:48

In the Enterprise Manager locate the table that you want the create script for and right click on it. Select All Tasks-->Generate SQL Script.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Re: &quot;show create table&quot; equivalent in MSSQL

karanba11-Dec-05 5:01

karanba

11-Dec-05 5:01

I know that but I need to do that with an sql command...

karanba

Re: "show create table" equivalent in MSSQL

Colin Angus Mackay11-Dec-05 5:15

Colin Angus Mackay

11-Dec-05 5:15

karanba wrote:
I know that but I need to do that with an sql command

To my knowledge you cannot.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Re: &quot;show create table&quot; equivalent in MSSQL

Fariborz Golara11-Dec-05 10:21

Fariborz Golara

11-Dec-05 10:21

Please see "CREATE TABLE" in "SQL Server Book Online"

*********************************************
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }

| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
)

[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

< column_definition > ::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]

< column_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ON {filegroup | DEFAULT} ] ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}

< table_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ASC | DESC ] [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}

**************************************
CREATE PROCEDURE Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO
EXEC Test1
GO

Here is the result set:

(1 row(s) affected)

Test1Col
-----------
1

(1 row(s) affected)

Test2Col
-----------
2

Fariborz Golara

Re: "show create table" equivalent in MSSQL

Colin Angus Mackay12-Dec-05 4:55

Colin Angus Mackay

12-Dec-05 4:55

He was asking how to generate the command via a script from data already in the database rather than create a table from scratch. Looking in BOL will not help him in this case because the answer is not there.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Re: &quot;show create table&quot; equivalent in MSSQL

Mike Dimmick12-Dec-05 5:34

Mike Dimmick

12-Dec-05 5:34

You can't do that, but you can query the table's schema using the INFORMATION_SCHEMA views. For example, to get the list of columns in a table, you can use

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=N'MyTable'

If you have multiple schemas (tables of the same name with different owners in the same database in SQL Server 2000) you should also supply the TABLE_SCHEMA in your select statement.

Stability. What an interesting concept. -- Chris Maunder

Re: &amp;quot;show create table&amp;quot; equivalent in MSSQL

karanba12-Dec-05 22:14

karanba

12-Dec-05 22:14

tahks for all..I am on search the subject..if any one heard about any sp about this please write here ..

karanba

focus on row

NikoTanghe9-Dec-05 22:50

NikoTanghe

9-Dec-05 22:50

What I want to do:
I have a listview, when the user clicks on a row and presses an edit
button, a dialog is shown and the user can change the row in the dataset.
The dialog contains several databound edit controls.

problem:
The dialog should focus on the right row. most of the time,
this is correct. But sometimes it is not. Not sure why, but it happens
when I add, remove rows from the dataset or sort the data.

Is this approach correct?
what should be the problem?

my code:

    <br />
Private Sub btnWICustomerEdit_Click(...) ...<br />
        Dim key As Integer = getkey()<br />
<br />
        'create form<br />
        Dim EditData As frmEditDataActivities = New frmEditDataActivities(...)<br />
<br />
        Dim rowIndex As Integer = dsCustomers.Tables(0).DefaultView.Find(Key)<br />
        If rowIndex = -1 Then<br />
            Return<br />
        End If<br />
<br />
        'focus on the right row<br />
        EditData.SetFocusRow(rowIndex, dsCustomers)<br />
        EditData.ShowDialog()<br />
<br />
        'do update, save changes<br />
    End Sub<br />
<br />
    'a member from the dialog class<br />
    Public Sub SetFocusRow(ByVal rowIndex As Integer, ByRef dsCustomers As dsCustomers)<br />
        Dim cm As CurrencyManager<br />
        cm = CType(Me.BindingContext(dsCustomers, dsCustomers.Tables(0).TableName), CurrencyManager)<br />
        cm.Position = rowIndex<br />
    End Sub<br />
<br />

question

eyalso9-Dec-05 22:05

eyalso

9-Dec-05 22:05

hi

i want to insert data into sql server (in C#)
one of the attributes (Article) id define as varchar (size 8000 - the MAX)
i want to insert into Article string lets call the variable S (size lower then 8000 and higher than 1000) but its doesnt work, but if S is define in size 100 its working .
if somebody knows what is the problem i will be glad if he will explain to
me .

eyalso

Re: question

Colin Angus Mackay10-Dec-05 7:34

Colin Angus Mackay

10-Dec-05 7:34

e_s wrote:
if somebody knows what is the problem

What is the error message?

You can create a row with a number of columns that, in total, excede the maximum row size (8006 bytes, if I remember correctly). You will get a warning when you create such a table structure. It is possible that with the other data in the row something has to give.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

ADO with VS.Net Query Failure when Time is selected

K.J.B.9-Dec-05 13:07

K.J.B.

9-Dec-05 13:07

I am using Visual Studio 2002 (Visual Basic) and an Access Database.
Some tables are read into a Dataset via oledbDataAdapters.

When I read fields from the datasets, I get good values for strings, numbers and dates or times.
When selecting from dataset tables using “myRows = myTable.Select(Filter, Sort)”, I get correct results unless I use a time field in the select criteria. Then 0 rows are returned.

The select clause is properly formatted. As soon as I drop the time field, I get returned rows. I have bypassed the problem by getting the data directly from the database (hard drive), but it is frustrating! A typical WHERE filter clause is:-

"ID = 12 AND StartTime = #07:00:00# AND ReportDate = #10/11/2005# AND ReportArea = 'Area A'"

A, probably related, anomaly is:
Using the “DataAdapter Preview – Fill” method (design mode), the time fields are represented as 30/12/1899 – (a null or base date?). Despite this, I do read times and dates from the dataset/tables correctly in code.

I’m fed up searching the, often overly extensive, help. “Can’t see the forest for the trees”

Any suggestions welcome!!

managing long data types

kasko_9-Dec-05 11:42

kasko_

9-Dec-05 11:42

i found in documentation such description but i couldnt find any functions or examples of using them or APIs to manage long data block-by-block... can you help me?

"When the ntext, text, and image data values get larger, however, they must be handled on a block-by-block basis. Both Transact-SQL and the database APIs contain functions that allow applications to work with ntext, text, and image data block by block."

Inconsistent Read issues

tsramkumar9-Dec-05 9:31

tsramkumar

9-Dec-05 9:31

Hi
I had a question abt the Inconsistent Read concurency issue with my application's data access that I am currently developing. Here is my problem. I am using custom business objects that represent my database entities. For example, in one of the web pages displays a list of users in a datagrid bindng it to customcollection that holds user objects (instance of my user class). I am using Enteprise Library to do my dataccess. my DAL would read the data from the database (makes a call to the store procedure) and pass the custom collection of user objects to the UI. When the user clicks on any row of the displayed list, it takes himt o another page that displays further details of that particular user and he may update them. Do I need to use any lock options while reading the data from the database? I mean optimistic concurrency control?

Any suggestions would be greatly appreciated...Please help.

Thanks

adp.Fill method pooling issues

Joshua Lunsford9-Dec-05 7:57

Joshua Lunsford

9-Dec-05 7:57

I am pretty much exclusively using SqlDataAdapter.Fill method.... I am getting the dreaded timeout connection pool limit exceeded. MSDN tells me I dont have to exclusively open and close my connections. Yet! under heavy use, i'm getting these errors in my application. What can I do to make sure these connections are getting closed using this method.

Re: adp.Fill method pooling issues

Farhan Noor Qureshi9-Dec-05 12:00

Farhan Noor Qureshi

9-Dec-05 12:00

I use

finally

block to make sure that clean up is performed

Farhan Noor Qureshi

^{if (this == this) thow this;}

Help - I'm a Novice - SQL Query Problem

kabatlantafan79-Dec-05 5:20

kabatlantafan7

9-Dec-05 5:20

I am trying to combine several columns from one table: The First Name, Last Name, Middle Name, City, State. I want to Combine the first, last, and middle names and then have a varying amount of spaces between it and the city and state. All of these will be combined into one column and displayed in a listbox. For Example:

Problem:
John Smith Los Angeles, CA
Johnathan Smith Los Angeles, CA

Solution: HOW!
John Smith Los Angeles, CA
Johnathan Smith Los Angeles, CA

Re: Help - I'm a Novice - SQL Query Problem

miah alom9-Dec-05 5:58

miah alom

9-Dec-05 5:58

Asuuming all are varchar or char(n)

select FirstName + ' ' + LastName + ' ' + MiddleName + ' ' + City + ' ' + State
from yourtable.

Re: Help - I'm a Novice - SQL Query Problem

Farhan Noor Qureshi9-Dec-05 6:03

Farhan Noor Qureshi

9-Dec-05 6:03

You might wanna check for null, b/c if any of the field is NULL then the resultant is NULL. Use TSQL function IsNull() function.

Farhan Noor Qureshi

^{if (this == this) thow this;}

Re: Help - I'm a Novice - SQL Query Problem

miah alom9-Dec-05 6:03

miah alom

9-Dec-05 6:03

right.

Differing Results between Record binding (C++) and Collect method

Stone Free9-Dec-05 3:42

Stone Free

9-Dec-05 3:42

Hi,

I have some code where the dataset being modified is the result of a JOIN between two tables. The key of the first table is also a foreign key of the second table. There will be situations where the fields from the second table will be NULL and others when the alternate key on table two will match the primary key of table one.

The record binding code works when the alternate key is not NULL, but when it and the second tables fields are NULL then the UpdateBatch gives the error "Row cannot be located for updating. Some values may have been changed since it was last read."

This is not true as this is a) not a multi-user system and b) nothing has changed.

I have dumped all the values in the record set - Value, OriginalValue and UnderlyingValue. All the fields of the second table are showing up as NULL for all of the value types.

All the other fields values are identical apart from Value for the attribute on the first table being updated.

I have status values for all the fields from the second table and they are all set to adFldNull.

If I then swap to using the Collect method then no error occurs.

The connection has the following properties:

Provider = oledb (Server is SQL Server 2000)
CursorLocation = adUseClient
IsolationLevel = adXactReadCommitted
ConnectionMode = adModeRead | adModeWrite | adModeShareDenyRead |
adModeShareDenyWrite | adModeShareExclusive

The recordset properties are:
CursorLocation = adUseClient
CursorType = adOpenStatic
LockType = adLockBatchOptimistic
MarshalOptions = adMarshalModifiedOnly

Any ideas other than swapping to using the Collect method?

Counter

vuthaianh8-Dec-05 17:08

vuthaianh

8-Dec-05 17:08

Can we restart the counter in SQL Server
If we can, how can we do it
Thanks

Re: Counter

Colin Angus Mackay8-Dec-05 20:22

Colin Angus Mackay

8-Dec-05 20:22

What counter?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Re: Counter

vuthaianh10-Dec-05 15:10

vuthaianh

10-Dec-05 15:10

It's AutoIncrement Field
Thanks

Re: Counter

Johny Ng12-Dec-05 8:20

Johny Ng

12-Dec-05 8:20

Well, AFAIK, you can set the IDENTITY_INSERT ON and then let say your max ID is 10 so if you want to set it back to 1, set the seed to -9, and set the seed back to 1.

ODBC rowset size

jia20048-Dec-05 9:21

jia2004

8-Dec-05 9:21

hello,

i have a question: i do a SELECT on a .mdb file. i want to get the no of rows in the rowset. SQLRowCount won't work - 'cos I've made a select.
i don't wanna fetch all rows and set a counter.

is there any way? i've tried:
retcode = SQLExtendedFetch(hStmt, SQL_FETCH_LAST, 0, &lRow, &lStatus);
//this goes ok
SQLGetStmtAttr(hStmt, SQL_ATTR_ROW_NUMBER, &noOfRows, SQL_IS_INTEGER, NULL);
printf("total:%ld, \n",noOfRows);
//this NOT!! it's 0.

i think there must be a way - anyway i must complain about ODBC's lack of *good* documentation.
thanks

Cristian

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.