Question when subtracting two queries

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.Problem resolved. Thinking was off.

Quote:

Originally Posted by parkc

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.

|||

Quote:

Originally Posted by parkc

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.

TRY below query and check if that works...

select * from
(
select h.name as 'DataBase Name',t.name as 'Table Name',c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h on h.schema_id = t.schema_id
inner join sys.columns c on t.object_id = c.object_id
) x
left outer join
(
--213
select h.name as 'DataBase Name',t.name as 'Table Name',c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t on s.major_id = t.object_id
inner join sys.schemas h on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
) y on x.[DataBase Name] = y.[DataBase Name]
where y.[DataBase Name] is null

Question on VBScript connection issue and SQL 2005

I have a script that performs a number of reiterative tasks very quickly on a SQL database. After a few thousand requests (2-3 minutes) I get the following error:

Error: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
Code: 80004005
Source: Microsoft OLE DB Provider for ODBC Drivers

The connection string looks like:

Driver={SQL Server};Server=45.22.11.33;Uid=test;Pwd=test;Database=Test

I have changed nothing and it always does this. It doesn't matter if in my script I open and close the connection on every request or leave it open the whole time.

Additionally, the script and the database are on the same machine.

Thanks for any help.There might be various reasons for this. How many concurrent user connections are made in your case? Are you sure you're not exceeding the limit per your license?|||

Is it possible that the IP address on the target machine was changed for some reasons? Can you try the following things?

1) ping 45.22.11.33.

2) telnet 45.22.11.33 1433

If any of this failed, you may have some network issue. Thanks.

Question on using patindex

Hello,

I am trying to update a column in a table with an 8 digit string.
The 8 digit number is obtained from another column which is a comments
field.

e.g.

Comments Field :

1) RD-V0216970 RQST NON SMKING KING Unknown # : 22338921
2) Received verbal authorization Authorization # 040345
3) international plaza, singapore # 96722540

The code that I am using is

UPDATE U SET U.NUM =
CASE
WHEN U.BOOKED_COMMENTS_TXT LIKE
('%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%')
THEN
SUBSTRING(U.BOOKED_COMMENTS_TXT,
PATINDEX('%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%',
U.BOOKED_COMMENTS_TXT), 8)
ELSE NULL
END
FROM UNKNOWN1_RESERVATIONS U

Here's what my result set looks like

1)V0216970
2)040345
3)96722540

But this is how I want my result set to look like

1)22338921
2)null
3)96722540

What I need is a way to restrict the search criteria to exactly 8
numeric digits. Any suggestions will be helpful.

Thanks in advanceSQL_developer (vlavanya@.gmail.com) writes:
> I am trying to update a column in a table with an 8 digit string.
> The 8 digit number is obtained from another column which is a comments
> field.
> e.g.
> Comments Field :
> 1) RD-V0216970 RQST NON SMKING KING Unknown # : 22338921
> 2) Received verbal authorization Authorization # 040345
> 3) international plaza, singapore # 96722540
> The code that I am using is
>...
> Here's what my result set looks like
> 1)V0216970
> 2)040345
> 3)96722540
> But this is how I want my result set to look like
> 1)22338921
> 2)null
> 3)96722540
> What I need is a way to restrict the search criteria to exactly 8
> numeric digits. Any suggestions will be helpful.

From the sample it appears that the string you are looking for is
always at the end of the comments field. In this case, this could do:

UPDATE tbl
SET col = CASE WHEN reverse(str)
LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]'
THEN right(str, 8)
ELSE NULL
END

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank your for your response Erland.

There are times when the string is in the middle or beginning of the
comments field. That's the reason I had to go with Patindex.
Is there anyway I can include the length check into the code ?|||On 31 Mar 2005 06:32:21 -0800, SQL_developer wrote:

>Thank your for your response Erland.
>There are times when the string is in the middle or beginning of the
>comments field. That's the reason I had to go with Patindex.
>Is there anyway I can include the length check into the code ?

Hi SQL_developer,

The reason your query failed is that you searched for 6 numeric
characters instead of 8. Changing that solves it. I also replace the
proprietary UPDATE FROM syntax with the much more portable ANSI-standard
UPDATE syntax - in this case, there is no reason at all to use UPDATE
FROM!

CREATE TABLE UNKNOWN1_RESERVATIONS
(ID int NOT NULL PRIMARY KEY,
NUM char(8),
BOOKED_COMMENTS_TXT varchar(200))
go
INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V0216970 RQST NON SMKING KING Unknown # : 22338921'
UNION ALL
SELECT 2, 'Received verbal authorization Authorization # 040345'
UNION ALL
SELECT 3, 'international plaza, singapore # 96722540 Not in the middle'
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN BOOKED_COMMENTS_TXT LIKE
'%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
THEN SUBSTRING(BOOKED_COMMENTS_TXT,

PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',
BOOKED_COMMENTS_TXT), 8)
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> The reason your query failed is that you searched for 6 numeric
> characters instead of 8. Changing that solves it.

Not really. Consider:

INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V02169709 RQST NON SMKING KING Unknown # : 22338921'

Do we really want the number after the RD-V?

Here is an improved version of your query - built on the assumption
that the number must be surrounded by blanks:

UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN BOOKED_COMMENTS_TXT + ' ' LIKE
'% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] %'
THEN SUBSTRING(BOOKED_COMMENTS_TXT,
PATINDEX('% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] %',
BOOKED_COMMENTS_TXT + ' ') + 1, 8)
ELSE NULL
END

> I also replace the proprietary UPDATE FROM syntax with the much more
> portable ANSI-standard UPDATE syntax - in this case, there is no reason
> at all to use UPDATE FROM!

So what! In the general case in SQL Server there is all reason to use
FROM in UPDATE statements, since it gives you so much power and flexibility.
Sure, for this query it's an overkill, but if you think that always
using an alias is good programming style, then you need a FROM clause.
(And that is just one of the reasons that FROM with UPDATE is so good,
it permits you to specify an alias for the table being updated.)

Yeah, it's correct, if your join conditions are incomplete, your UPDATE
will be imcomplete. And I've seen more than one who had gone wrong with
his correlated subqueries. Nevermind that the performance even with
correct correlated subqueries is generally a lot poorer.

I really wish everyone would stop people from bashing people from using
FROM in UPDATE statements. This is a newsgroup for MS SQL Server. If
you don't like FROM in UPDATE clauses, go elsewhere.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 31 Mar 2005 21:26:02 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> The reason your query failed is that you searched for 6 numeric
>> characters instead of 8. Changing that solves it.
>Not really. Consider:
> INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
> SELECT 1, 'RD-V02169709 RQST NON SMKING KING Unknown # : 22338921'
>Do we really want the number after the RD-V?
>Here is an improved version of your query - built on the assumption
>that the number must be surrounded by blanks:
(snip)

Hi Erland,

Thanks for that!

I assumed that the OP wanted to use the first 8-digit number. Upon
rereading his post, I now see that he writes "exactly 8 numeric digits".

Let's hope that your assumption about the number always being surrounded
by spaces is correct. If not, a further change would be:

UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN ' ' + BOOKED_COMMENTS_TXT + ' ' LIKE

'%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%'
THEN SUBSTRING(BOOKED_COMMENTS_TXT,

PATINDEX('%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%',
' ' + BOOKED_COMMENTS_TXT + ' '), 8)
ELSE NULL
END
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you for your response Erland and Hugo!

I used the UPDATE FROM statement because this code will be used as a
step in a DTS package and hence I need all the flexibility I can get :)
.. I also find that using an Alias makes it easy for me to read my code
at a later point of time.

The table I am getting my comments field from, gets updated daily and I
will be running the DTS to pick up the 8 digit number every week.
As for the number itself, it may or may not have blanks surrounding it.
I used Hugo's code and it seems to work for most cases.
There are a few cases where the comments field looks like

" Confirmation number 4234235608532, Room for 4"

So when I run this code, it picks up the first 8 digits. But I don't
want it to pick up the 8 digits in this case. It should return null
when the number is greater than 8 digits. Sorry I didn't mention this
earlier. It's just that there are so many records and I keep running
into more situations like these as I modify the code.

Thanks for all your help!|||On 31 Mar 2005 14:36:23 -0800, SQL_developer wrote:

(snip)
>I used Hugo's code and it seems to work for most cases.
>There are a few cases where the comments field looks like
>" Confirmation number 4234235608532, Room for 4"
>So when I run this code, it picks up the first 8 digits. But I don't
>want it to pick up the 8 digits in this case. It should return null
>when the number is greater than 8 digits.
(snip)

Hi SQL_developer,

Did you test it with the code in my first post (addressed to you), or
the code in my second post (replied to Erland)? The latter *SHOULD*
exclude the example above - but I've already shut down my server, so I
can't test it now. Let me know if you saw this happening with the code
in my second post - it's probably a typo; I'll look into it tomorrow.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,

I used the code from your second post. To make it easier for you to run
it in a query analyser I will post the entire code to you.

CREATE TABLE UNKNOWN1_RESERVATIONS
(ID int NOT NULL PRIMARY KEY,
NUM char(8),
BOOKED_COMMENTS_TXT varchar(200))
go
INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V0216970 RQST NON SMKING KING Unknown # : 22338921'
UNION ALL
SELECT 2, 'Received verbal authorization Authorization # 0403455'
UNION ALL
SELECT 3, 'international plaza, singapore #96722540Not in the middle'
UNION ALL
select 4, 'Confirmation number 4234235608532, Room for 4'
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN '' + BOOKED_COMMENTS_TXT + '' LIKE
'%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
THEN
SUBSTRING(BOOKED_COMMENTS_TXT,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', '' +
BOOKED_COMMENTS_TXT + ''), 8)
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go

Thanks again!|||Hello Hugo,

I used the code from your second post. To make it easier for you to run
it in a query analyser I will post the entire code to you.

CREATE TABLE UNKNOWN1_RESERVATIONS
(ID int NOT NULL PRIMARY KEY,
NUM char(8),
BOOKED_COMMENTS_TXT varchar(200))
go
INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V0216970 RQST NON SMKING KING Unknown # : 22338921'
UNION ALL
SELECT 2, 'Received verbal authorization Authorization # 0403455'
UNION ALL
SELECT 3, 'international plaza, singapore #96722540Not in the middle'
UNION ALL
select 4, 'Confirmation number 4234235608532, Room for 4'
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN '' + BOOKED_COMMENTS_TXT + '' LIKE
'%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
THEN
SUBSTRING(BOOKED_COMMENTS_TXT,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', '' +
BOOKED_COMMENTS_TXT + ''), 8)
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go

Thanks again!|||On 31 Mar 2005 14:53:50 -0800, SQL_developer wrote:

>Hello Hugo,
>I used the code from your second post. To make it easier for you to run
>it in a query analyser I will post the entire code to you.
(snip)

Hi SQL_developer,

The code you posed was NOT the code from my second post; it was a mix of
code from my first and second post. I've copied the script from your
post into Query Analyzer and corrected the errors - here it is:

Note - use copy and paste to copy it to QA at your end, don't copy it by
hand. One of the errors in your version was that you changed ' ' (quote,
space, quote) to '' (quote, quote). These spaces are very significant!
Another error was that you left out the additional [^0-9] (note the
caret!!) in the LIKE and PATINDEX pattern.

CREATE TABLE UNKNOWN1_RESERVATIONS
(ID int NOT NULL PRIMARY KEY,
NUM char(8),
BOOKED_COMMENTS_TXT varchar(200))
go
INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V0216970 RQST NON SMKING KING Unknown # : 22338921'
UNION ALL
SELECT 2, 'Received verbal authorization Authorization # 0403455'
UNION ALL
SELECT 3, 'international plaza, singapore #96722540Not in the middle'
UNION ALL
select 4, 'Confirmation number 4234235608532, Room for 4'
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN ' ' + BOOKED_COMMENTS_TXT + ' ' LIKE
'%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%'
THEN
SUBSTRING(BOOKED_COMMENTS_TXT,
PATINDEX('%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%', ' ' +
BOOKED_COMMENTS_TXT + ' '), 8)
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,

Thanks for your reply.

For some reason when I copy and paste data from the post to the DTS
package the Space between the Quotes disappears. That explains why I
did not have the spaces in my code.

I'm sorry about the missing Claret in my code. But I tested the latest
version and it works like a charm. Thank you so much for all your help!

question on table structure

All,

On my aspx page i have 2 sets of checkboxlists, each with a number of items and i am storing that info in my db. currently i have my table set up this way:

InfoID--PK auto

UserID-int

CheckBoxList1ID-int

CheckBoxList2ID-int

So a user can select more than 1 item in each checkbox, so this table will contain multiple user-ids and each id from the respective checkboxes. My question is, is there a better way to store this data? I hope this is clear, any input would be greatly appreciated.

Thank you.

I would have a table that holds the values for the checkboxes, and another table that bridges the user table and the checkbox value table. This would only have two fields - the user ID and the selected checkbox ID. This is a better way to manage many-to-many relationships. You would have to iterate through the checkboxes and insert a row for each one the user selected.http://www.mikesdotnetting.com/Article.aspx?ArticleID=53

|||

Thanks mike..actually I do have those other 2 tables as well, and I am pretty much doing it like that link you sent, just didn't know if there was a better way. Since thats the way to go, i am happy i don't have to recode.. . thanks for the response.

Question on System and Data Restore

new to SQL Server 2000. We have an obsolete database that we need to
save off for x number of years. DB2 has utilities (DB2Look/Export)
that allows for the export of the data along with a schema and script
that enables the future recreation of the structure of the databases
and tables to include RI etc. You can save off the architecture and
relationships of the tables as well as the data.

Does SQL Server have anything similar?

Thanks in advance.

GerryDataPro (datapro01@.yahoo.com) writes:

Quote:

Originally Posted by

new to SQL Server 2000. We have an obsolete database that we need to
save off for x number of years. DB2 has utilities (DB2Look/Export)
that allows for the export of the data along with a schema and script
that enables the future recreation of the structure of the databases
and tables to include RI etc. You can save off the architecture and
relationships of the tables as well as the data.
>
Does SQL Server have anything similar?

I don't see why you would anything else than a normal database backup?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In DB2, to save off data and structure we use utilties db2look and
db2export.

I can see that in SQL Server that I can script (ddl) the objects and
relationships and export the data. Its, I believe a matter of
redundancy more than anything else/

Thanks

Erland Sommarskog wrote:

Quote:

Originally Posted by

DataPro (datapro01@.yahoo.com) writes:

Quote:

Originally Posted by

new to SQL Server 2000. We have an obsolete database that we need to
save off for x number of years. DB2 has utilities (DB2Look/Export)
that allows for the export of the data along with a schema and script
that enables the future recreation of the structure of the databases
and tables to include RI etc. You can save off the architecture and
relationships of the tables as well as the data.

Does SQL Server have anything similar?

>
I don't see why you would anything else than a normal database backup?
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||DataPro (datapro01@.yahoo.com) writes:

Quote:

Originally Posted by

In DB2, to save off data and structure we use utilties db2look and
db2export.
>
I can see that in SQL Server that I can script (ddl) the objects and
relationships and export the data. Its, I believe a matter of
redundancy more than anything else/

There are of course situations where you want to duplicate a schema or
copy the data from one database to another.

But since you talked about future recreation, it sounded more like a
backup to me.

Note that for development you should keep all your SQL code under
version control. If you do this, there is rarely any reason to script
from the database, since the version-control system holds the truth
about the system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Question on repeated query

I have to select a number of fields for a report. Name Address, city,
state zip, etc. from a list of customers.
I have to select 2000 records from each of 29 states.
Is there a way to do this without repeating and repeating the code in
QA, or by changing the state manually and running it each time?
I have to select 2000 customers from WV, 2000 from VA, 2000 from FL,
2000 from TX and so on.
It's more of just a random selection of customers, with no other
requirements other than the name & address are a good one (i.e. non-null
last name, good zip code, etc).
Any help appreciated.Try,
select
Name Address, city, state, zip
from
t1 as a
where
customerid in (
select top 2000 customerid
from t1 as b
where b.state = a.state
)
go
If you run this query again, do not expect to pull same result because I am
not using "order by" clause.
AMB
"Blasting Cap" wrote:

> I have to select a number of fields for a report. Name Address, city,
> state zip, etc. from a list of customers.
> I have to select 2000 records from each of 29 states.
> Is there a way to do this without repeating and repeating the code in
> QA, or by changing the state manually and running it each time?
> I have to select 2000 customers from WV, 2000 from VA, 2000 from FL,
> 2000 from TX and so on.
> It's more of just a random selection of customers, with no other
> requirements other than the name & address are a good one (i.e. non-null
> last name, good zip code, etc).
> Any help appreciated.
>

question on ntext datatype in sql server

What is the max. number of characters in ntext?

Are there any way we can format the output of ntext? Or it will just come out as one long line?

Thanks.

Its just data of the following definition:

ntext

Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-2003 synonym for ntext is national text.

formatting is up to you after retrieving it from the database.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Question on displaying large number of record?

I have datagrid that needs to display a log table which has more than million records.

Since it it huge number, it is not possible to get dataset using "select * from log_table" to fill and to bind to datagrid.

Is there anyway to display first 100 rows on first page and show next 100 rows if use clicks on page 2?

Thank you very much in advance!

Justin

since its simply not feasible to bring that much data back from sql, you should look at performing the paging logic in sql server itself.
sql would only return one page of data at a time

here's some links to see how it can be done:

http://weblogs.asp.net/pwilson/archive/2003/10/10/31456.aspx

http://www.sqlmag.com/articles/index.cfm?articleid=40505

http://www.4guysfromrolla.com/webtech/062899-1.shtml

i'm sure there are plenty more examples floating around..

|||

You could use the TOP clause in your query

or

You could use .net assemblies with SQL server......(CREATE ASSEMBLY ...)

|||I am using Oracle but thank you anyway... I got idea...

|||

In PL/SQL you can use row_number() function, or if you don't need to sort the records, simply use the ROWNUM column.

Question on Data/structure restore

new to SQL Server 2000. We have an obsolete database that we need to
save off for x number of years. DB2 has utilities (DB2Look/Export)
that allows for the export of the data along with a schema and script
that enables the future recreation of the structure of the databases
and tables to include RI etc. You can save off the architecture and
relationships of the tables as well as the data.

Does SQL Server have anything similar?

Failing that, our plan is to backup the data and logs then image the entire disk.

Thanks in advance.

GerryNot exactly sure what you want.

SQL Server 2000 of course has backup and restore capability, and the Enterprise Manager utility has the ability to script database objects and relationships.

A lot depends upon why you are archiving the data and what its intended use is.|||OK. If you had to save off a database...both the data and the 'structure' of the tables, relationships between tables etc....for possible recreation years down the road...how would you do it?|||sql server has a backup wizard, backup the DB to a BKF file and put it wherever, you can restore it just as easily with the wizard - table structures and all the data

also, the design of the database should be documented in the technical specs in a word doc. so you could recreate the whole thing from documentation if necessary|||Look up BACKUP in Books online

Do you have the SQL Server Client tools installed?|||Also look here

http://weblogs.sqlteam.com/tarad/archive/2004/08/04/1876.aspx|||also, the design of the database should be documented in the technical specs in a word doc. so you could recreate the whole thing from documentation if necessaryIn a Word document?
Just script the database ddl to a text file.|||I believe the scipting of the ddl of the objects in addition to a backup AND a data export is a desired redundancy. I belive the scripting capabilties of SS should do the job. That's for all the interesting info .

Gerry|||Should read Thanks :)